Solved

Oracle 10g with index slower than no index for select

Posted on 2011-02-15
2
486 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 11

Accepted Solution

by:
Akenathon earned 500 total points
ID: 34895821
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
ID: 34897714
If the number of rows returned by the query are less... rebuilding the index will help.

alter index <indexname> rebuild
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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 how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

623 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