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

Adam Tadjkarimi wrote on 28-Mai-99 16:40:30:
>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?

Well, AFAIK the order does not matter, as long as you supply a value for every PK column.
If you just select for a part of the key, you have to supply the first columns of the key in order to get the PK undex used.

To be really sure you should consider the execution plan using explain plan or set autotrace on (if that still exists in O8) If the plan indicates the use of the PK index you are right but otherwise the select could take qite awhile.

>Platform: Oracle 8.04 on NT4.0

>Any prompt input will be appreciated! Thanks in ad!

Hope that helps,
Lothar

--

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Schulstr. 12            | lothar.armbruester_at_t-online.de
D-65375 Oestrich-Winkel |