Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle 10g with index slower than no index for select

Posted on 2011-02-15
2
Medium Priority
?
500 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 2000 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

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…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

661 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