Re: Does order of a PK columns in a SELECT matter in the speed of the query?

From: Ravi Verma <ranju-ravi_at_worldnet.att.net>
Date: 1 Jun 1999 23:14:25 GMT
Message-ID: <01beac84$cc483f60$a2694e0c@Verma.medco.com>

Oracle is very particular about the order in cases where composite indexes are available.
In order for a query to use the index, rather than go for a full table scan, the where clause should have the columns in the order that they are indexed.

AV

---

> >Say I have a table as Tab1 with columns col1, col2, col3, col4, col5,
> >... and it has a compound PK as PK_tab1 (col1, col2,col3). Now I run
> >the following SELECTs:
> >1- SELECT * FROM Tab1 WHERE col1='X' AND col2=10 and col3='Y';
> >2- SELECT * FROM Tab1 WHERE col2=10 AND col3='Y' and col1='X';
> >.or any possible combination...
> >Which one will execute faster? or the order they appear in the WHERE
> >clause doesn't matter and optimizer will take care of it?
Received on Tue Jun 01 1999 - 18:14:25 CDT