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: