-
indexing question
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?
-
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.
Last edited by kris109; 01-12-2004 at 03:16 PM.
Remember the Golden Rule - He who has the gold makes the rules!
===================
Kris109
Ph.D., OCP 8i, 9i, 10g, 11g DBA
-
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
-
No, This is a general question.
-
well if cardinality is high enough, therefore index selectivity, why would you want to create composite index narrowing down your index usage possibilities?
-
Re: indexing question
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...
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
Composite index is only beneficial in case of getting only data from the index itself without accessing the tables, thus save I/O.
-
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.
Jeff Hunter
-
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.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|