Home » SQL & PL/SQL » SQL & PL/SQL » Order of INDEXES
Order of INDEXES [message #19541] Tue, 26 March 2002 10:29 Go to next message
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 Go to previous message
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.
Previous Topic: HOW INCREASE THE SIZE OF A TABLE
Next Topic: Fetching backward from cursor?????Urgent
Goto Forum:
  


Current Time: Thu Mar 28 07:13:57 CDT 2024