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

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

 

Advertisements
  1. March 5, 2009 at 12:27 pm

    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/

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Don Charisma

because anything is possible with Charisma

Carol no Mundo!

Aventuras de uma intercambista a trabalho pelo mundo!

Blog do PHP

Oracle DBA for rookies!

nimaidba

Welcome to the world of Oracle with me....

Tech

News and reviews from the world of gadgets, gear, apps and the web

WordPress.com

WordPress.com is the best place for your personal blog or business site.

Gurcan Orhan's ODI and DWH Blog

Some words about Oracle Data Integrator and Data Warehousing.

%d bloggers like this: