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 - 
10) in the inner view and referencing this new column in query of the outer. 
But this is apparently not the desired solution.



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



Something like this:

create or replace view v3

as

select c1,c2,c3

from test_p_v

where c2 >= 210 and c2 <= 309 and c1 >= 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 <= 99

;

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



select * from v3

where c2 = 150;

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

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

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

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

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

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

|   2 |   UNION-ALL PARTITION      |          |       |       |            | 
|       |       |

|*  3 |    FILTER                  |          |       |       |            | 
|       |       |

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

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

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

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

|*  8 |    FILTER                  |          |       |       |            | 
|       |       |

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

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

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





Predicate Information (identified by operation id):

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



   3 - filter(210<=150)

   5 - filter("C2"=150 AND "C2">=210 AND "C2"<=309)

   7 - filter("C2"=150 AND "C2">=110 AND "C2"<=209 AND "C1"<=209)

   8 - filter(110>150)

  10 - filter("C2"=150 AND "C2"<110 AND "C1"<=99)



A good question is if the obviously false filter  (e.g. 210<=150) does 
prohibit the underlying  table access. Some simple tests shows that this is 
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 
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 = 550

SQL> /



Elapsed: 00:00:00.01



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



  1  select count(*) from v3

  2* where c2 = 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 = upper('test_p_v');



PARTITION_NAME                     BLOCKS

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

P1                                  18765

P2                                  19785

P3                                  19913



Elapsed: 00:00:03.34

----- Original Message ----- 
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




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

Other related posts: