Re: Optimizing a Simple 'select count(*)'
Date: Fri, 7 Mar 2014 19:58:29 +0000
Message-ID: <CF3F8D07.550FA%Mark.Bobak_at_ProQuest.com>
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<mailto:Michael.J.Tefft_at_snapon.com>>
Date: Friday, March 7, 2014 at 2:23 PM
To: Mark Bobak <Mark.Bobak_at_ProQuest.com<mailto:Mark.Bobak_at_ProQuest.com>>, "fmhabash_at_gmail.com<mailto:fmhabash_at_gmail.com>" <fmhabash_at_gmail.com<mailto:fmhabash_at_gmail.com>>, "oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>" <oracle-l_at_freelists.org<mailto: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 Fri Mar 07 2014 - 20:58:29 CET