Missing optimization when dealing with parittioned range partitions + MIN()/MAX() queries ?

From: Nilo Segura <nilosegura_at_gmail.com>
Date: Tue, 8 Feb 2011 17:53:38 +0100
Message-ID: <AANLkTimtz2Mm_BbwdhXFc6XYbrsjmsbWMDh9Jvk8fbQB_at_mail.gmail.com>

Hello,

In this test case (copied from Tom Kyte's site, thanks!)

 CREATE TABLE table2000 ( x int, y int, z DATE)  PARTITION BY RANGE (z)

     (
     PARTITION tab_1999_h1 VALUES LESS
     THAN(to_date('30-jun-1999','dd-mon-yyyy')),

    PARTITION tab_1999_h2 VALUES LESS
    THAN(to_date('31-dec-1999','dd-mon-yyyy')),    PARTITION tab_2000_h1 VALUES LESS
    THAN(to_date('30-jun-2000','dd-mon-yyyy')),    PARTITION tab_2000_h2 VALUES LESS
    THAN(to_date('31-dec-2000','dd-mon-yyyy'))     )

insert into table2000  values ( 1, 1, '15-jun-1999' );
insert into table2000  values ( 2, 2, '15-dec-1999' );
insert into table2000  values ( 3, 3, '15-jun-2000' );
insert into table2000  values ( 4, 4, '15-dec-2000' );

commit

exec dbms_stats.gather_Table_Stats(null,'TABLE2000',no_invalidate=>false);

Now I want to get the MIN() of the partition key column for all the table

explain plan for select min(z) from table2000;

---

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

---

| 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)|

00:00:01 |       |       |


|   1 |  PARTITION RANGE ALL|           |     1 |     8 |            |

         |     1 |     4 |


|   2 |   SORT AGGREGATE    |           |     1 |     8 |            |

         |       |       |


|   3 |    TABLE ACCESS FULL| TABLE2000 |     4 |    32 |     3   (0)|

00:00:01 |     1 |     4 |
-------------------------------------------------------------------------------------------------

PARTITION RANGE ALL ? This particular query is equivalent to

explain plan for select min(z) from table2000 partition (tab_1999_h1);

---

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

---

| 0 | SELECT STATEMENT | | 1 | 8 | 2

(0)| 00:00:01 |       |       |


|   1 |  PARTITION RANGE SINGLE|           |     1 |     8 |

 |          |     1 |     1 |


|   2 |   SORT AGGREGATE       |           |     1 |     8 |

 |          |       |       |


|   3 |    TABLE ACCESS FULL   | TABLE2000 |     1 |     8 |     2

(0)| 00:00:01 |     1 |     1 |
----------------------------------------------------------------------------------------------------

However the optimizer does not seem to recognize this optimization. I ran the test on 10.2.0.5 and 11.2.0.1 . Same can be applicable to MAX() . If I create an index on the partition key

 create index z_idx on table2000 (z) local;

the explain plan then

---

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

---

| 0 | SELECT STATEMENT | | 1 | 8 | 1

(0)| 00:00:01 |       |       |


|   1 |  PARTITION RANGE ALL        |       |     1 |     8 |

  |          |     1 |     4 |


|   2 |   SORT AGGREGATE            |       |     1 |     8 |

  |          |       |       |


|   3 |    INDEX FULL SCAN (MIN/MAX)| Z_IDX |     1 |     8 |     1

(0)| 00:00:01 |     1 |     4 |
-----------------------------------------------------------------------------------------------------

and again with the partition name

 explain plan for select min(z) from table2000 partition (tab_1999_h1);

---

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

---

| 0 | SELECT STATEMENT | | 1 | 8 | 1

(0)| 00:00:01 |       |       |


|   1 |  PARTITION RANGE SINGLE     |       |     1 |     8 |

  |          |     1 |     1 |


|   2 |   SORT AGGREGATE            |       |     1 |     8 |

  |          |       |       |


|   3 |    INDEX FULL SCAN (MIN/MAX)| Z_IDX |     1 |     8 |     1

(0)| 00:00:01 |     1 |     1 |
-----------------------------------------------------------------------------------------------------

So it seems that the optimizer is not able to take advantage of the info stored in the dictionary for this particular case and goes for partition range all instead of scanning just one partition (the "initial" one).

If the index is however global, the INDEX FULL SCAN (MIN/MAX) works correctly (as expected).

Bug or Enhancement report ?

regards.

-- 
Nilo Segura
Oracle Support - IT/DB
CERN - Geneva
Switzerland
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 08 2011 - 10:53:38 CST