RE: Optimizing a Simple 'select count(*)'
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 10 Mar 2014 08:01:48 +0000
Matt,
I did think about that for a while - and there is a case where the optimizer
will ignore the lowest cost path if it involves a range scan using unknown
values, which would be the case with unpeeked bind variables. (See, for
example: http://jonathanlewis.wordpress.com/2010/09/07/cbo-surprise-3/ ).
However the rationale for that is that some other path may have a fixed cost
(X, say) when the range scan might actually result in a workload whose cost
should have been much higher than X.
That being the case, I could imagine Oracle doing a (fixed cost) index fast
full scan when we expected an index range scan, but I couldn't think of a
reason why it might
do a tablescan instead of an index fast full scan. (Apart from the - generally
unlikely - case that the index was actually bigger than the table for some
reason).
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________________
From: McPeak, Matt [vxsmimmcp@xxxxxxxxxx]
Sent: 09 March 2014 22:34
To: Jonathan Lewis; oracle-l@xxxxxxxxxxxxx
Subject: RE: Optimizing a Simple 'select count(*)'
Just throwing this out there...
It wouldn't have anything to do with the fact that the from and to dates are
bind variables for which we don't know the values? If the initial values given
were broad enough, wouldn't Oracle (rightly) choose a FTS over an index?
Matt
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Jonathan Lewis [jonathan@xxxxxxxxxxxxxxxxxx]
Sent: Sunday, March 09, 2014 11:25 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Optimizing a Simple 'select count(*)'
Even without seeing the code, but assuming there is an index that includes the
required data column referenced in the predicate, it does seem a little odd
that Oracle has chosen a full tablescan rather than (worst case) an index fast
full scan.
The only obvious reason I can think of for this choice is that either the index
has grown to a much larger size than it should be, or the only available index
includes so many of the table columns that it actually HAS to be larger than
the table.
select blocks from user_tables where table_name = ...
select leaf_blocks from user_indexes where index_name = ...
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Jared Still [jkstill@xxxxxxxxx]
Sent: 09 March 2014 13:14
To: fmhabash@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Optimizing a Simple 'select count(*)'
On Wed, Mar 5, 2014 at 11:56 AM, Fred Habash
<fmhabash@xxxxxxxxx<mailto:fmhabash@xxxxxxxxx>> wrote:
So, why should we live with such costly resource consumptions for such a simple
query?
We don't know enough about the tables and/or indexes.
If you include complete code for a reproducible test case, you will likely get
more response.
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Sr Oracle DBA at Pythian
Pythian Blog http://www.pythian.com/blog/author/still/
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
