Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Why my index is not used?

 
Bruce Jin
Ranch Hand
Posts: 672
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Carol Enderlin
drifter
Ranch Hand
Posts: 1364
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
they should?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic