composite index....when used?

Hi,

I create a composite index IDX on (col1,col2,col3,...).
Witch queries will use this index?


For example following queries will use IDX?
select..... where col1=val1; or
select..... where col2=val2 and col3=val3;
....


Oracle version V8i/9i
Thank's
Bensmail

Hi,
The first query would use index becuase col1 is the leading column in the composite index.

regards
anandkl

Just curious,

I thought the 9i optimizer would use the 2nd or 3rd key alone in a composite index.

Ken

I read in oracle-base.com the query use the index in all cases (if it can) in Oracle V 9i (INDEX SKIPPING)
I hope i understand the contents.


Thank's
Bensmail

Yup, an index skip scan can be used where the query isn't selective on the leading column(s) of the index, but you'd have to make sure that there are few distinct values for those leading columns. A fast full index scan could also be used, I guess.

Quote:

---

Originally posted by bensmail
I read in oracle-base.com the query use the index in all cases (if it can) in Oracle V 9i (INDEX SKIPPING)
I hope i understand the contents.


Thank's
Bensmail

---

indeed you can sometimes merily skip scan your way through a query

This may help me with a problem I'm having rebuilding an index online (key too big error).

I have INDEX123
TABLE1
COLUMN1
COLUMN2
COLUMN3

IF I create INDEX321 (9i, so online)
TABLE1
COLUMN3
COLUMN2
COLUMN1

Then drop INDEX123

IF performance holds up with a "SKIP SCAN", I have effectively rebuild online, right?

Am I missing something?

I'd test this right now, but my test database has been importing for a 2 weeks and should finish in a few days (clobs take foreeeveeerr to import).

Ken

Quote:

---

Originally posted by slimdave
A fast full index scan could also be used, I guess.

---

Only when select list dosent have columns other than indexed..

Abhay.