Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Optimizing Oracle queries
- Thread starter jmannix
- Start date
Scunningham99
Programmer
ONLY THE ORDER OF COLUMNS DETERMINE WEATHER INDEX IS USED
Sy UK
Sy UK
Scunningham99, AFAIK this is no more the case. At least since 8.1.7.
Regards, Dima
Regards, Dima
Scunningham99
Programmer
thaught i read it somewhere as part of 9i ocp track
havent got the book to confirm
ta
s
Sy UK
havent got the book to confirm
ta
s
Sy UK
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.
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.
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
Regards, Dima
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.
Regards, Dima
Regards, Dima
- Status
Similar threads
- Locked
- Question
- Locked
- Question
