RE: Curious as to why this index isn't being used
- From: <Jay.Miller@xxxxxxxxxxxxxxxx>
- To: <ahmusch@xxxxxxxxx>
- Date: Wed, 21 Apr 2010 11:00:52 -0400
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:
