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

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Sat, 8 Mar 2014 09:11:30 -0500
Message-ID: <085d01cf3ad8$4ec8c080$ec5a4180$_at_rsiz.com>



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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark Bobak
Sent: Friday, March 07, 2014 2:58 PM
To: Tefft, Michael J; fmhabash_at_gmail.com; oracle-l_at_freelists.org 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_at_snapon.com> Date: Friday, March 7, 2014 at 2:23 PM
To: Mark Bobak <Mark.Bobak_at_ProQuest.com>, "fmhabash_at_gmail.com" <fmhabash_at_gmail.com>, "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> 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  

--

http://www.freelists.org/webpage/oracle-l Received on Sat Mar 08 2014 - 15:11:30 CET

Original text of this message