RE: Anyway to optimize the optimizer

  • From: "Khedr, Waleed" <Waleed.Khedr@xxxxxxx>
  • To: "jaromir nemec" <jaromir@xxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 3 Mar 2005 17:33:19 -0500

I thought about the partitioned view, but it will be ugly plus Oracle is
not planning to continue to support partitioned views.

Thanks

Waleed

-----Original Message-----
From: jaromir nemec [mailto:jaromir@xxxxxxxxxxxx]=20
Sent: Thursday, March 03, 2005 5:28 PM
To: Khedr, Waleed; oracle-l@xxxxxxxxxxxxx
Subject: Re: Anyway to optimize the optimizer


Hi Waleed,



> Oracle does not substitute the value of C2 in the expression.



this could be achieved with two nested views using an expression e.g.
(c2 -=20
10) in the inner view and referencing this new column in query of the
outer.=20
But this is apparently not the desired solution.



I'd see a possible solution of this problem in a combination of
partitioned=20
views and partitioned tables (Jonathan mentioned it in his book, if I
recall=20
it correctly).



Something like this:

create or replace view v3

as

select c1,c2,c3

from test_p_v

where c2 >=3D 210 and c2 <=3D 309 and c1 >=3D 200

union all

select c1,c2,c3

from test_p_v

where c2 between 110 and 209 and c1 between 100 and 209

union all

select c1,c2,c3

from test_p_v

where c2 < 110 and c1 <=3D 99

;

So each UNION ALL subquery access at most 2 partitions. Note that this
is=20
only simplified solution to demonstrate the principle.



select * from v3

where c2 =3D 150;

Explaining  this simple query you get following execution plan
(10.1.0.2.0):

------------------------------------------------------------------------
-------------------------------

| Id  | Operation                  | Name     | Rows  | Bytes | Cost
(%CPU)|=20
Time     | Pstart| Pstop |

------------------------------------------------------------------------
-------------------------------

|   0 | SELECT STATEMENT           |          |   483 | 14490 |   342
(21)|=20
00:00:01 |       |       |

|   1 |  VIEW                      | V3       |   483 | 14490 |   342
(21)|=20
00:00:01 |       |       |

|   2 |   UNION-ALL PARTITION      |          |       |       |
|=20
|       |       |

|*  3 |    FILTER                  |          |       |       |
|=20
|       |       |

|   4 |     PARTITION RANGE SINGLE |          |     1 |    27 |  7622
(6)|=20
00:00:07 |     3 |     3 |

|*  5 |      TABLE ACCESS FULL     | TEST_P_V |     1 |    27 |  7622
(6)|=20
00:00:07 |     3 |     3 |

|   6 |    PARTITION RANGE ITERATOR|          |    60 |  1740 | 18148
(21)|=20
00:00:17 |     2 |     3 |

|*  7 |     TABLE ACCESS FULL      | TEST_P_V |    60 |  1740 | 18148
(21)|=20
00:00:17 |     2 |     3 |

|*  8 |    FILTER                  |          |       |       |
|=20
|       |       |

|   9 |     PARTITION RANGE SINGLE |          |     1 |    26 |  7193
(6)|=20
00:00:07 |     1 |     1 |

|* 10 |      TABLE ACCESS FULL     | TEST_P_V |     1 |    26 |  7193
(6)|=20
00:00:07 |     1 |     1 |

------------------------------------------------------------------------
-------------------------------





Predicate Information (identified by operation id):

---------------------------------------------------



   3 - filter(210<=3D150)

   5 - filter("C2"=3D150 AND "C2">=3D210 AND "C2"<=3D309)

   7 - filter("C2"=3D150 AND "C2">=3D110 AND "C2"<=3D209 AND =
"C1"<=3D209)

   8 - filter(110>150)

  10 - filter("C2"=3D150 AND "C2"<110 AND "C1"<=3D99)



A good question is if the obviously false filter  (e.g. 210<=3D150) does =

prohibit the underlying  table access. Some simple tests shows that this
is=20
OK and at most one union branch is executed if a equi-predicate on c2 is

defined. See below.

You may wont to verify some more complex queries on this view, usage of
bind=20
variables etc.



HTH



Jaromir D.B. Nemec

http://www.db-nemec.com



---- some test --

1) c2 out of range - instance responce



  1  select count(*) from v3

  2* where c2 =3D 550

SQL> /



Elapsed: 00:00:00.01



2) test partition pruning, only the 1st partition should be accessed --
>=20
OK, consistent gets corresponds to blocks of the partition



  1  select count(*) from v3

  2* where c2 =3D 109

SQL> /



Elapsed: 00:00:04.32





Statistics

----------------------------------------------------------

          8  recursive calls

          0  db block gets

      18778  consistent gets

      18758  physical reads

          0  redo size

        392  bytes sent via SQL*Net to client

        512  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed



SQL> set autotrace off

SQL> select partition_name,

  2  blocks

  3  from user_tab_partitions  a

  4  where

  5  table_name =3D upper('test_p_v');



PARTITION_NAME                     BLOCKS

------------------------------ ----------

P1                                  18765

P2                                  19785

P3                                  19913



Elapsed: 00:00:03.34

----- Original Message -----=20
From: "Khedr, Waleed" <Waleed.Khedr@xxxxxxx>
To: "jaromir nemec" <jaromir@xxxxxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, March 03, 2005 3:37 AM
Subject: RE: Anyway to optimize the optimizer



--
//www.freelists.org/webpage/oracle-l

Other related posts: