Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Anyway to optimize the optimizer

Re: Anyway to optimize the optimizer

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Thu, 3 Mar 2005 23:27:58 +0100
Message-ID: <042501c52040$41b58ca0$3c02a8c0@JARAWIN>


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

  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

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 03 2005 - 17:32:12 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US