Optimizing Oracle queries

[jmannix]

Does anyone know if the order by which you place clauses in the WHERE section of a query can improve the performance of a SQL statement?

Thanks,

Joe

 

[sem]

No.

Regards, Dima

http://seminihin.narod.ru

 

[Scunningham99]

ONLY THE ORDER OF COLUMNS DETERMINE WEATHER INDEX IS USED

Sy UK

 

[sem]

Scunningham99, AFAIK this is no more the case. At least since 8.1.7.

Regards, Dima

http://seminihin.narod.ru

 

[Scunningham99]

thaught i read it somewhere as part of 9i ocp track

havent got the book to confirm

ta
s


Sy UK

 

[dbtoo2001]

I don't believe the hypothesis that "ONLY THE ORDER OF COLUMNS DETERMINE WEATHER INDEX IS USED " can hold true.
What drives the use of the index is based on what is in the select statement and what is in the predicate (and at 7.3 order matters, if you are still using 7.3)
Consider this example. I select two columns, the order of the select statement does not drive index used, but rather which column in the predicate is used:
09:36:10 ORIONCOP@orionssg01:SQL> select distinct order_id, order_no from sfwid_order_desc where order_id > 'new_order';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=29 Card=1 Bytes=
20)

1 0 SORT (UNIQUE NOSORT) (Cost=29 Card=1 Bytes=20)
2 1 INDEX (RANGE SCAN) OF 'SFWID_ORDER_DESC_INDX004' (NON-UN
IQUE) (Cost=3 Card=2 Bytes=40)




09:36:45 ORIONCOP@orionssg01:SQL> select distinct order_id, order_no from sfwid_order_desc where order_no > 'new_order';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=30 Card=1 Bytes=
20)

1 0 SORT (UNIQUE) (Cost=30 Card=1 Bytes=20)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'SFWID_ORDER_DESC' (Cos
t=4 Card=2 Bytes=40)

3 2 INDEX (RANGE SCAN) OF 'SFWID_ORDER_UNQ_ORDERNO' (UNIQU
E) (Cost=3 Card=2)




09:37:10 ORIONCOP@orionssg01:SQL> select distinct order_no, order_id from sfwid_order_desc where order_no > 'new_order';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=30 Card=1 Bytes=
20)

1 0 SORT (UNIQUE) (Cost=30 Card=1 Bytes=20)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'SFWID_ORDER_DESC' (Cos
t=4 Card=2 Bytes=40)

3 2 INDEX (RANGE SCAN) OF 'SFWID_ORDER_UNQ_ORDERNO' (UNIQU
E) (Cost=3 Card=2)




09:37:38 ORIONCOP@orionssg01:SQL> select distinct order_no, order_id from sfwid_order_desc where order_id > 'new_order';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=29 Card=1 Bytes=
20)

1 0 SORT (UNIQUE NOSORT) (Cost=29 Card=1 Bytes=20)
2 1 INDEX (RANGE SCAN) OF 'SFWID_ORDER_DESC_INDX004' (NON-UN
IQUE) (Cost=3 Card=2 Bytes=40)




09:38:12 ORIONCOP@orionssg01:SQL> select distinct order_id, order_no from sfwid_order_desc where order_id > 'new_order';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=29 Card=1 Bytes=
20)

1 0 SORT (UNIQUE NOSORT) (Cost=29 Card=1 Bytes=20)
2 1 INDEX (RANGE SCAN) OF 'SFWID_ORDER_DESC_INDX004' (NON-UN
IQUE) (Cost=3 Card=2 Bytes=40)


When I remove the use of a predicate, it will only use 1 index regardless of the order:
09:43:36 ORIONCOP@orionssg01:SQL> select order_no, order_id from sfwid_order_desc;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=6457 Card=693452
Bytes=13869040)

1 0 INDEX (FULL SCAN) OF 'SFWID_ORDER_DESC_INDX004' (NON-UNIQU
E) (Cost=6457 Card=693452 Bytes=13869040)




09:43:51 ORIONCOP@orionssg01:SQL> select order_id, order_no from sfwid_order_desc;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=6457 Card=693452
Bytes=13869040)

1 0 INDEX (FULL SCAN) OF 'SFWID_ORDER_DESC_INDX004' (NON-UNIQU
E) (Cost=6457 Card=693452 Bytes=13869040)

To answer jmannix's question - turn on trace and experiment.

 

[sem]

Scunningham99, just try it on 8i: create compound unique index and play with order of conditions (equations). Access path (UNIQUE SCAN) does not depend on the order.

Regards, Dima

http://seminihin.narod.ru

 

[sem]

I must revisit this thread and make my excuses: performance may depend on predicate order. This article is about 9i, but the only difference is the lack of dbms_xplan in pre-9i.

http://otn.oracle.com/pub/articles/lewis_cbo.html

Regards, Dima

http://seminihin.narod.ru