Showing results for 
Search instead for 
Did you mean: 


Go to solution
malay boy
Trusted Contributor


I'm a bit confuse.We have a complex selest statement which select 3 big table.The statement run for 30 min and finish.
Next the user delete some index on that table and he told me the queries now run faster.He told me that too many index will cause the query to be slow.
Is this true OR the guy is bluffing me ?.

There are three person in my team-Me ,myself and I.
Honored Contributor

Re: index

It can be that by deleting a index the sql query run's faster.
I have seen it in the past with a Oracle 7.3.4 database that was configured with ruled based optimization. In that way it depended on the existing index's what search path it took.
When this was a realy silly path for that specific query it took very long to finish.

We dicided to reconfigure the data to cost based optimization. (This is the default in Oracle 8.1.x > )

Hopes this helps.

Elmar P. Kolkman
Honored Contributor

Re: index

It depends on the select statement. If the statement only does selects it should not slow down the query, but if you do inserts/updates in the query, every index will slow down the query.
Every problem has at least one solution. Only some solutions are harder to find.

Re: index

As Peter said, the key point is optimization method on this issue. Sometimes optimizers may choose wrong methods to retrieve data from the tables. BTW, extra indices on a table may affect negative while inserting or updating data but positive effect in retrieving data if the optimizer chooses appropriate index.
never ever give up.
Zafar A. Mohammed_1
Trusted Contributor

Re: index

Its all depend on the Optimizer and ofcourse sometimes too many indexes will run very slow. So, try not to use unnecessary indexes.

Jean-Luc Oudart
Honored Contributor

Re: index

I don't think that too many indexes will slow the query. (too many indexes would impact update statements).

It's more Oracle using the wrong index(es) for the query.
(From a previous post I understand the database is Oracle !).

use explain plan before and after removing the index(es) and compare the plan.
From table size (and so on) you should understand why this is faster.
You can also run a trace on the query (before/after) and analyze the trace files with tkprof (use explain here too).

Also, this can be that the statistics on indexes/tables where not updated and Oracle chose the "wrong" path based on its "known" statistics.

let us know what are your findings.

fiat lux
Hein van den Heuvel
Honored Contributor

Re: index

As the others wrote, your end user might be right, but it is indicative of a problem
(aka: missed opportunity!).

I have experienced this in a a (Siebel) benchmark where there was an index S_AB on colums A and B and index S_BA on columns B and A. Column A, had only 100 distinct values with a stong skew to 1 value. Column B had 50,000 unique values.

So for a (sub)query WHERE A=:a AND B=:b it is best to use index S_BA, not S_AB.

The rule based optimizer does not know this, but the cost based optimizer would know... if up-to-date statistics are present.

If you are stuck with the rule based optimizer, then either go ahead and drop that index, or have the applicatio write add a query HINT to suggest Oracle to use a specific index. Something along the lines of:
SELECT /*+ INDEX ( table [index [index]...] ) */ WHERE blah blah
USe the Oracle DOc, or Google for details and full examples.

Zafar, Indeed extra (excessive!) indices will make the cost based optimizer do more work, but normally that shoudl be a minimal component compared to the actuall execution.

Malay boy,
Final resolution about your problem as to the cause can come from an 'explain plan'.

For further, detailed help, you'll need to provide at least 3 from the following missing information: Oracle version, )global/session) optimizer setting, index descriptions, explain plan outputs, table statistics settings and so on.
Honored Contributor

Re: index

Hi mB,

Hein van den Heuvel summarised it well.

You should have a look at the actual execution plan, the statistics and so on. I firmly believe it is all about FULL Table Scan v/s index usage performance.

Allow me to me to quote this nice description of how it works....

A full tablescan reads the table using large sequential reads -- many blocks at a time. Sequential reads are the fastest type of IO you can do on a disk in general.

An index read will do single block, random IO's. These are generally the slowest you can do.

If you are going to read 25% of the table via an index you will probably hit MOST if not all of the blocks in the table. You will hit some blocks many times probably as well.

Ok, if you take the above as "fact" we can move on... Lets say the table is 100 blocks, with 1000 values and your buffer cache can hold 50 blocks. Let's say you will read this via an index. We will read 250 rows.

The index might tell us "goto block 1, get row 1", "goto block 2, get row 1" ... "goto block N get row 1".... "goto block 100 get row 1"

So, so far, the index has had us read each block once and get the first row on each block. The index processes the data in sorted order, not in the order it physically resided on the disk in the table. Hence, we did 100 random IO's to read 100 blocks in. Not only that -- but blocks 1-50 are no longer in the buffer cache, blocks 51-100 are. Now, resuming our query (we have 150 more rows to get!) the index tells us: "goto block 1, get row 2", "goto block 2, get row 2" ... "goto block N get row 2".... "goto block 100 get row 2"

Oh no -- we just got told to RE-READ the blocks we already read!!! Again!!! from Disk!! So, assuming the table had no blocks in the cache, we've now just done 200 random physical IO's against that table -- AND we are not done yet!!!

So, picking up where we left off -- the index now says "goto block 1, get row 3", "goto block 3, get row 1" ... "goto block N get row 3".... "goto block 50 get row 3". Bummer, 50 more random physical IO's.

Now, the full table scan comes in. Suppose our db_file_multiblock_read_count is set to 20. We do 5 sequential IO's of 20 blocks each and answer the query. Done. Period. Much faster.

Here, a full table scan does a whole lot less IO more efficiently.

best regards
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)