RE: Curious as to why this index isn't being used
- From: <Jay.Miller@xxxxxxxxxxxxxxxx>
- To: <wmorgan@xxxxxxxxxxxxxxxxx>, <ahmusch@xxxxxxxxx>
- Date: Wed, 21 Apr 2010 11:25:30 -0400
Yes, I looked into that but as you can see from the information I
provided below there are 1,825 distinct values out of 495,000 rows
without a histogram. I would have thought that the difference between
selecting 2 rather than 1 value out of 1,825 wouldn't be enough to swing
it away from using the index which is why I suspect it's something to do
with one of the values being null. I have no idea what that might be
however :) and was hoping for some understanding of what was going on.
Jay Miller
Sr. Oracle Database Administrator
201.369.8355
________________________________
From: Will Morgan [mailto:wmorgan@xxxxxxxxxxxxxxxxx]
Sent: Wednesday, April 21, 2010 11:07 AM
To: Miller, Jay; ahmusch@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Curious as to why this index isn't being used
I can't view your data - but perhaps the inclusion of the literal and
the NULLs pushes the number of rows to be returned larger than would be
practical for the optimizer to index scan - and therefore it would full
scan - which is more efficient when returning > a certain percentage of
the rows of the table.
Just my 1st thought.
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
Jay.Miller@xxxxxxxxxxxxxxxx
Sent: Wednesday, April 21, 2010 10:01 AM
To: ahmusch@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: 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:
