Thanks for the question, Venkatesh.
Asked: March 30, 2016 - 2:19 am UTC
Last updated: March 31, 2016 - 3:09 am UTC
Version: 11g
Viewed 1000+ times
You Asked
Hi,
Is it a good idea to create a multiple index for same column (in different set) below is example...
table: ABC
columns:
A VARCHAR2(3),
B VARCHAR2(18),
C VARCHAR2(50),
.
.
.
G TIMESTAMP,
H CHAR(1)
CREATE INDEX IDX1_ABC ON ABC
(A, B, G);
CREATE INDEX IDX2_ABC ON ABC
(G);
CREATE INDEX IDX3_ABC ON ABC
(H);
In above example i'm creating index for Column G in two places... Because i'm using index 1 in join table and index 2 in where clause.
Please advice
and Connor said...
Every index should be added with one simple rule in mind:
Does the benefit outweigh the cost ?
The benefits *might* be:
- speeds up certain queries
- avoids locking issues (eg a foreign key index)
- avoids a sort (order by <indexed_col>
- avoids a visit to the table ( select A, B from ... , and there is an index on A,B )
versus the costs of:
- might slow down other queries
- slows down transaction speed
- larger database size (longer backups etc)
So look at getting as much *value* from your indexes as possible. For example, if you had three indexes:
G
G,H
G,H,I
then this probably means the first two can be omitted, because they share the same leading columns as the third.
But if you had H,I, then you *might* still need it, if your app has a specific need for accessing data by H and I.
Hope this helps.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment