RE: Composite Index Order VS Query Order ?? , Tuning Docs URL ?.

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Tue, 13 Jul 2004 10:40:50 -0500
Message-ID: <009501c468ef$c96d1c20$6701a8c0@CVMLAP02>

No. The order of keys in a composite index does NOT need to be in the same order as the ANDed predicates in your WHERE clause. Now, if you = have an ORDER BY clause, it will speed your query if the columns there and the composite index columns are listed in the same order (because it will = save a sort operation).
Check Dan Tow's "Tuning SQL" book, published by O'Reilly and = Associates.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Upcoming events:
- Performance Diagnosis 101: 7/20 Cleveland, 8/10 Boston, 9/14 San =
Francisco
- SQL Optimization 101: 7/26 Washington DC, 8/16 Minneapolis, 9/20 =
Hartford
- Hotsos Symposium 2005: March 6-10 Dallas

Visit www.hotsos.com for schedule details...

-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Ranjeesh K R.
Sent: Tuesday, July 13, 2004 9:54 AM
To: undisclosed-recipients:
Subject: Composite Index Order VS Query Order ?? , Tuning Docs URL ?.

Hi,
I was working on optimising a piece of code which was taking 10hrs + to execute and all.So my queries are..

#1) Saw a table where the "order of query" on the table and the "order = of
key"
are different . For faster results shouldn't they be in the same order.

for eg:

Select ... where A.ID =3D ... and A.Name =3D ...

The Key order on this table A NOW is Name & ID Order .=20 Shouldn't they be in the order ID , Name ?..

#2) Can anybody recommend me any site , where I will get reliable information on Tuning PL/SQL ?.

With thanks in advance=20

Ranjeesh

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Jul 13 2004 - 10:40:08 CDT