Order of INDEXES [message #19541] |
Tue, 26 March 2002 10:29 |
uma
Messages: 67 Registered: May 2001
|
Member |
|
|
This is really interesting to know more about INDEXES.
What is the order should be while creating INDEXES.If I have a table TABLE and columns as col1,col2,col3,col4.
Suppose My Primary key is col1 and most often I query to see the col3(99%) and sometimes(60%)against column2.So my order of indexes should be
(col2,col3,col1) or (col1,col3,col2).
1.what is the order should be?
2. Is this order really matters in the query.
Means If my select query is like this
select col4,col3
where col1=9
and col3='AA'
or my query should be like this
select col4,col3
where col3='AA'
and col1=9
3. Is it better to create 2 set of indexes?
one is with (col1,col2,col3) and other (col1,col2)
I am really interested to know the answer and info about the indexes.
Uma
|
|
|
Re: Order of INDEXES [message #19542 is a reply to message #19541] |
Tue, 26 March 2002 11:45 |
Suresh Vemulapalli
Messages: 624 Registered: August 2000
|
Senior Member |
|
|
1) it depends on your where clause
all index columns or leading columns should be present in where clause ,then only
oracle uses that index.
Example:
if u create index on col1,col2,col3 (composite index ) and try following query..
a)select * from tab1 where col1=1 and col3=3
oracle will not use index, it goes for full table scan
b)select * from tab1 where col1=1 and col2=2
in this case, oracle uses index.
2) order doesn't matter
3) in your example, col1 is already indexed(because it is primary key).
create 2 separate indexes on col2 and col3.
|
|
|