Thursday, March 5, 2009

When did Optimizer use index FFS in queries having Max(datetime)?

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:

H.Tonguç Yılmaz said...

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/

Windows (powershell) counterparts of Linux commands

You may find Windows mostly powershell equivalent of frequently used Linux commands here. I will update this post, with newer ones by the ti...