Re: Optimizing a Simple 'select count(*)'

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Wed, 5 Mar 2014 12:54:17 -0800 (PST)
Message-ID: <1394052857.70711.YahooMailNeo_at_web124701.mail.ne1.yahoo.com>


http://dfitzjarrell.wordpress.com/?s=tale+of+two+indexes

 
David Fitzjarrell
Primary author, "Oracle Exadata Survival Guide"




On , David Fitzjarrell <oratune_at_yahoo.com> wrote:
 
http://dfitzjarrell.wordpress.com/?s=tale+of+two+indexes
 
David Fitzjarrell
Primary author, "Oracle Exadata Survival Guide"




On Wednesday, March 5, 2014 11:05 AM, Mark Bobak <Mark.Bobak_at_proquest.com> wrote:
 
If there is not a NOT NULL constraint on the completion_time column, it’s possible not all rows will be in index based on that column, so optimizer has to do FTS.

-Mark

From: "fmhabash_at_gmail.com" <fmhabash_at_gmail.com>
Reply-To: "fmhabash_at_gmail.com" <fmhabash_at_gmail.com>
Date: Wednesday, March 5, 2014 at 12:56 PM
To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>
Subject: Optimizing a Simple 'select count(*)'


select count (*) from my_table where completion_time between to_date(:"SYS_B_0",:"SYS_B_1") and to_date(:"SYS_B_2",:"SYS_B_3")


so does these variations of it ...


select (my_col) where my_col is PK

select (rowid) ...

select count(1) ...

select /*+ index(my_index) */ 


This takes 1630769 PIOs, 1630774 LIOs, 165 secs and does an FTS on my_table.


My research led me to the following ...

1) As of 8i, count(*) is the same as count(rowid), count(1), & count(my_col). This is true conceptually & practically. 

2) count(*) should be served via a fast full index scans. Why is it not in this example?

3) There are options to rely on CBO stats to get row count, but this happens at the expense of accuracy. 


So, why should we live with such costly resource consumptions for such a simple query?





Thank you ...
----------------------------------------
Fred Habash, Database Solutions Architect (Oracle OCP 8i,9i,10g,11g)
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 05 2014 - 21:54:17 CET

Original text of this message