Oracle 10g with index slower than no index for select

Hi Experts,

I am doing an experiment of a dataset of over 100,000 rows of record where the where condition are 100% unique and sorted by default in the database.

However, when I ran my select query, it seemed that queries with index are actually slower than queries without index on that field.

That field is based on NUMBER(8,4) datatype. Would appreciate any advice. pls.
chongchianAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
AkenathonConnect With a Mentor Commented:
As a generic answer, there are at least two advantages of the full table scan: it avoids reading index blocks and it uses multiblock reads. That means an index can well slow down your queries in many cases, including but NOT AT ALL limited to:

- Bad selectivity or filtering factor: You have too many values for the index key(s) you are querying, thus you end up reading so much of your table than it would have been faster to save the trouble of reading the index first, and while you are at it use multiblock reads to scan the table
- Bad clustering factor: Your table happens to have the data for the index key(s) you are querying in different Oracle blocks for the most part. That means you end up reading so many single table blocks than it would have been faster to read them in multiblock reads (that's what a full table scan does), and while you are at it save the trouble of reading the index first

So much for the concepts... if you need specific advice on your specific case, you need to give us a way to reproduce your specific scenario. At the very least, please post the create table, create unique index and select statements to reproduce the problem, plus some sample data and the execution statistics both with and without the unique index -get those with SET AUTOTRACE ON in sqlplus.
0
 
jaiminpsoniCommented:
If the number of rows returned by the query are less... rebuilding the index will help.

alter index <indexname> rebuild
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.