-
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
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.
-
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
I'm stmontgo and I approve of this message
-
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
-
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
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
|