Solved

Oracle 10g with index slower than no index for select

Posted on 2011-02-15
2
477 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:chongchian
2 Comments
 
LVL 11

Accepted Solution

by:
Akenathon earned 500 total points
Comment Utility
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
 
LVL 5

Expert Comment

by:jaiminpsoni
Comment Utility
If the number of rows returned by the query are less... rebuilding the index will help.

alter index <indexname> rebuild
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now