RE: Optimizing a Simple 'select count(*)'
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 5 Mar 2014 15:41:17 -0500
In which case (column is not constrained NOT NULL),
select count(*) from my_table where completion_time is not null and
completion_time between to_date(:"SYS_B. should find the index just fine IF
there is an index on completion_time.
Then is should become a question of cost, since any order will do, cluster
factor should not enter into it (just size).
Unless something bizarre preceded this, it seems likely the index would win.
If there is no index that wins though, you've got to waddle through all the
blocks below the high water mark to count and filter the rows, which
includes any now empty blocks along the way.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Mark Bobak
Sent: Wednesday, March 05, 2014 1:05 PM
To: fmhabash@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Optimizing a Simple 'select count(*)'
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@xxxxxxxxx" <fmhabash@xxxxxxxxx>
Reply-To: "fmhabash@xxxxxxxxx" <fmhabash@xxxxxxxxx>
Date: Wednesday, March 5, 2014 at 12:56 PM
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
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)
Other related posts:
