RE: Optimizing a Simple 'select count(*)'
From: Tefft, Michael J <Michael.J.Tefft_at_snapon.com>
Date: Fri, 7 Mar 2014 19:23:25 +0000
Message-ID: <8CA507E7F87805479C5C3DF54AA713A761E1ACCB_at_LISL-XMBS-13-PP.snaponglobal.com>
It is not necessary for completion_time to be NOT NULL for a index on that column to be suitable for this query.
Date: Fri, 7 Mar 2014 19:23:25 +0000
Message-ID: <8CA507E7F87805479C5C3DF54AA713A761E1ACCB_at_LISL-XMBS-13-PP.snaponglobal.com>
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-lReceived on Fri Mar 07 2014 - 20:23:25 CET