Why my index is not used?
posted 15 years ago
-
-
Number of slices to send:Optional 'thank-you' note:
-
-
These 2 queries take 23 seconds on Oracle:
(1) select invline from mrcmpower.bisales order by invline desc
(2) select invline from mrcmpower.bisales where invline >0 order by invline desc
They should be the same because all 2 million rows in the table have invline > 0.
Then I added index: create index mrcmpower.index2 on mrcmpower.bisales (invline desc)
Now query (1) still takes 23 seconds and query (2) only takes 0.01 second.
Why query (1) does not use the index?
Thanks
(1) select invline from mrcmpower.bisales order by invline desc
(2) select invline from mrcmpower.bisales where invline >0 order by invline desc
They should be the same because all 2 million rows in the table have invline > 0.
Then I added index: create index mrcmpower.index2 on mrcmpower.bisales (invline desc)
Now query (1) still takes 23 seconds and query (2) only takes 0.01 second.
Why query (1) does not use the index?
Thanks
BJ - SCJP and SCWCD
We love Java programming. It is contagious, very cool, and lot of fun. - Peter Coad, Java Design
Crazy Bikes created by m-Power
| Danger, 10,000 volts, very electic .... tiny ad: Paul Wheaton's 16th Kickstarter: Gardening playing cards for gardeners and homesteaders https://coderanch.com/t/889615/Paul-Wheaton-Kickstarter-Gardening-playing |