RE: Curious as to why this index isn't being used

Yes, as you say "adding a literal to the index key allows the optimizer
to find all the NULL values for dispatch_lock_uid to be found using the
index".  My question is why the index is not used when I search on both
a null and a literal when it is used for either one on its own.
 
 
 

Jay Miller 
Sr. Oracle Database Administrator 
201.369.8355 

 

________________________________

From: Adam Musch [mailto:ahmusch@xxxxxxxxx] 
Sent: Tuesday, April 20, 2010 6:36 PM
To: Miller, Jay
Cc: <oracle-l@xxxxxxxxxxxxx>
Subject: Re: Curious as to why this index isn't being used


Entirely NULL index keys (the value being indexed in a non-unique index)
are never indexed in a B-tree index, so adding a literal to the index
key allows the optimizer to find all the NULL values for
dispatch_lock_uid to be found using the index.  The NULL values are
either first or last in the index; I don't remember which.   

Sent from my iPhone

On Apr 20, 2010, at 3:50 PM, <Jay.Miller@xxxxxxxxxxxxxxxx> wrote:



        I learned the trick of appending a 1 to an index to include null
values at the Hotsos symposium this year and was looking forward to
trying it out on a query where I thought it might do some good.
Changing a single SQL requires about 6 months or so of testing but
slipping a new index in is relatively simple.
         
        So here's the index:
        CREATE INDEX message_transmission_idx01
        on message_transmission(dispatch_lock_uid,1);
         
        And here's the query:
        UPDATE  message_transmission m 
        SET m.dispatch_lock_uid = 'xxxx'
           WHERE (m.dispatch_lock_uid = 'PENDING' OR m.dispatch_lock_uid
IS NULL)
           AND rownum <= 2000;
         
        This does not use the index I created.  However 
        
        UPDATE  message_transmission m 
        SET m.dispatch_lock_uid = 'xxxx'
           WHERE (m.dispatch_lock_uid = 'PENDING');
        and
        UPDATE  message_transmission m 
        SET m.dispatch_lock_uid = 'xxxx'
           WHERE (m.dispatch_lock_uid IS NULL);
         
        both use the index.   So the null values are definitely
included. 
         
        If I change it from an UPDATE to a SELECT the first version will
do a fast full index scan while the second 2 will do (much faster) range
scans.
         
        495,000 rows and 1,825 distinct values.  No histogram.
        Oracle 10.2.0.4 on SLES 10 Linux.
         
        I'm trying to understand why this would be the case.  Any ideas?

Other related posts: