• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 515
  • Last Modified:

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.
0
chongchian
Asked:
chongchian
1 Solution
 
AkenathonCommented:
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now