RE: Optimizing a Simple 'select count(*)'
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <Mark.Bobak@xxxxxxxxxxxx>, "'Tefft, Michael J'" <Michael.J.Tefft@xxxxxxxxxx>, <fmhabash@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 8 Mar 2014 09:11:30 -0500
Good point. I also brain cramped on this: the is not null clause is only
needed for the index to be considered if there is no other predicate on the
column(s) of the index in question (apart from is null, which won't make
sense in this case.)
Including an "is not null" clause is useful in some predicate drop out
clause software when you want fast full scan to be considered even if the
predicate that could lead to a range scan is omitted.
This can be useful for the queries that happen after a form filled in with
lots of "any" left in before the query to run is chosen. It may also be
useful documentation of the fact that the query in question is only looking
for non-null values in that use.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Mark Bobak
Sent: Friday, March 07, 2014 2:58 PM
To: Tefft, Michael J; fmhabash@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Optimizing a Simple 'select count(*)'
You're right. I don't know what I was thinking. The NOT NULL constraint
business would only make any sense if you were doing a count(*) on all rows
in the table. Then the optimizer would look for an index on a column that
has a NOT NULL constraint (or a 'column_name is not null' predicate in the
query) before it would use the index.
I need to read more closely before I start typing..sorry.
-Mark
From: <Tefft>, Michael J <Michael.J.Tefft@xxxxxxxxxx>
Date: Friday, March 7, 2014 at 2:23 PM
To: Mark Bobak <Mark.Bobak@xxxxxxxxxxxx>, "fmhabash@xxxxxxxxx"
<fmhabash@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Subject: RE: Optimizing a Simple 'select count(*)'
It is not necessary for completion_time to be NOT NULL for a index on that
column to be suitable for this query.
Suppose you have rows that have completion_time as null? So what? That
predicate will exclude them anyway - and the optimizer knows this, and it
will gladly use an index on completion_time if it exists (and if it makes
sense).
The biggest question is: Does an index actually exist, with that column?
I don't think that has not been explicitly stated, and I have had (a few)
occasions when I struggled for hours with the question 'why won't it use the
index on that column' because I assumed it had to exist, but never verified
it was really there.
Mike
Other related posts:
