Too Many Indices?

[NaughtyVicar]

From the standpoint of query performance in an ad-hoc data warehousing environment, can you have too many indices on a table?

If I have 100 fields on a table and index 50 of them, will performance become degraded because Oracle has to find which index to use when creating it's query plan?

Sorry if this is a dumb question but I do need an answer.

Much thanks in advance!
-Rich

 

[THoey]

If the query is a SELECT query, it shouldn't have too much degradation. But if data is altered or deleted it will slow it down. Also, if you do any mass loading (SQL*Loader) it will take forever as it has to alter each of those indexes for each record loaded. Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[AOLEURO]

Hi

It is possible to have too many indexes on the system then using DML operations and also some DDL (move) operations. Performance degree because overhead during operation.

And you fill up space you also need to backup and so on.

However you can identify unused indexes in Oracle 9i and benefits space conservation and improve performance by eliminating unnecessary overhead during DML operations.

Before anything - update analyze statistics for the table and all indexes, with:

execute dbms_stats.gather_table_stats('USERNAME','TABLE_NAME');

First you activate monitoring on EACH index you want to monitor (all indexes on table minus primary index), with:

alter index INDEX_NAME monitoring usage;

You then query the table v$OBJECT_USAGE to see if indexes had been used. A column USED show YES or NO.

Of cause you monitor then you got diff. DML statements and pure select statements on/with the table to understand then indexes on the table(s) are used.

Then you stop monitoring then type:

ALTER INDEX INDEX_NAME NOMONITORING USAGE

If you are using ROLE based application on your system you will se a lot of YES in column USED, but with COST based you benefit using the above monitoring. Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !

 

[cliousa]

"If I have 100 fields on a table and index 50 of them, will performance become degraded because Oracle has to find which index to use when creating it's query plan?"

Not at all. It's the opposite - Oracle will know which index to use, if you are using CBO and have the recent statistics in place. The query will perform much faster if index is used, and not the FTS.

But for OLTP systems having too much indexes means - every time a record is inserted or updated, the index has to be updated too, which takes time. Also when doing DB maintenance - too much indexes can make you work longer....


Hope that helps,

clio_usa - OCP DBA
------------------

http://www.dbaclick.com

 

[NaughtyVicar]

How does Oracle find which index to use in it's query plan?

 

[Turkbear]

Hi,
When tables are analyzed that information enables the CBO to determine which is the least costly access method -
Note,however, that regardless of the # of indexes, if the rows returned are > 30% or so ( don't have the docs with the exact % with me) of the total rows in the table, a FTS will be done...

 

[AOLEURO]

Hi

Oracle can get data in (almost) 12 diff. ways.

One of the fastest is:

1. All select columns exists in the same index
2. All important where clause conditions are in the same index
3. If using a ORDER BY, then use it with the same order as the index

During parse, Oracle understand the above and just use the index WITHOUT access to the table - even if it is all rows (Fast Full Index Scan). It is very fast, because all information is in the index.

But as I started - Oracle can access DATA in almost 12 diff. ways - and of cause the Optimizer select the access path with lowest cost.

Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !