indexing question

[dba_admin]

Currently, I have 3 indexes on table t1:

idx1 on column c1
idx2 on column c2
idx3 on column c3

I plan to create a composite index on table t1 on columns c1, c2, c3. Then, I will drop
idx1, idx2, idx3.

Question:

Is this always a good practice for indexing? I mean, create composite index to replace individual index.

My biggest concern is : After I make this change, if I have a query on NON-Leading column on the composite
index, will Oracle use the composite index?

[kris109]

Depends on your db version and your where clauses. 9.0.1 and above oracle will skip scan indexes whose leading column does not appear in the where clause reducing the need for creation of too many indexes.

[slimdave]

Are you doing this to solve a specifically-identified problem?



Behaviour in this regard is version dependant -- if your oracle version supports index skip scans, then queries on a non-leading column can use the index provided that it is structured so that the left-hand columns of the index have a low number of distinct values

[dba_admin]

No, This is a general question.

[pando]

well if cardinality is high enough, therefore index selectivity, why would you want to create composite index narrowing down your index usage possibilities?

[julian]

Originally posted by dba_admin
Currently, I have 3 indexes on table t1:

idx1 on column c1
idx2 on column c2
idx3 on column c3

I plan to create a composite index on table t1 on columns c1, c2, c3. Then, I will drop
idx1, idx2, idx3.

I wouldn't do that...

[james_cc]

Composite index is only beneficial in case of getting only data from the index itself without accessing the tables, thus save I/O.

[marist89]

Originally posted by james_cc
Composite index is only beneficial in case of getting only data from the index itself without accessing the tables, thus save I/O.

Huh? Explain.

[james_cc]

If your index is consisted of daterder number:customer, then you can run a query just base on those index and get any of those data quicker.

[slimdave]

Originally posted by james_cc
If your index is consisted of daterder number:customer, then you can run a query just base on those index and get any of those data quicker.

That's true, but it's not the only purpose of composite indexes.