Missing optimization when dealing with parittioned range partitions + MIN()/MAX() queries ?
- From: Nilo Segura <nilosegura@xxxxxxxxx>
- To: Oracle List <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 8 Feb 2011 17:53:38 +0100
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
