Assume a Table MYTABLEA1, having date type column for example datetime:
select max(datetime) from MYCHEMA.MYTABLEA1;
Row count: 1384
Plan | ||
SELECT STATEMENT CHOOSE | ||
| 2 SORT AGGREGATE | |
|
| 1 TABLE ACCESS FULL MYCHEMA.MYTABLEA1 |
select max(datetime) from MYCHEMA.MYTABLEA2;
Row count: 8139221
Plan | |||
SELECT STATEMENT CHOOSE Cost: 442,815 Bytes: 8 Cardinality: 1 | |||
| 3 SORT AGGREGATE Bytes: 8 Cardinality: 1 | ||
|
| 2 PARTITION RANGE ALL Partition #: 2 Partitions accessed #1 - #26 | |
|
|
| 1 INDEX FULL SCAN (MIN/MAX) NON-UNIQUE MYCHEMA.MYTABLEA2 Cost: 442,815 Bytes: 2.491.053.600 Cardinality: 311.381.700 Partition #: 2 Partitions accessed #1 - #26 |
I quoted folowing from Julian.
The indexed columns must have a NOT NULL constraint
The table does not need to be analysed
http://www.juliandyke.com/Optimisation/Operations/IndexFullScanMinMax.html
1 comment:
nice coincidence, I also wrote on the importance of constraints for CBO minues ago :)
http://tonguc.wordpress.com/2009/03/05/how-constraints-may-affect-cost-based-optimizers-choises/
Post a Comment