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