| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re[2]: why the index is not used ?
--IMA.Boundary.4953586590
Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit Content-Description: cc:Mail note part
Steve:
But if you compare the response time with the RULE hint & CBO, there are a lots
differences.
see following query, it's almost 15 times fast by using RULE hint.
RAMSPRD>
1 SELECT T.BUSINESS_UNIT_ID, T.TRANSFER_ID, T.TRANSFER_OCCURENCE_TYPE,
2 T.TRANSFER_TYPE, T.FROM_SITE_ID, T.TO_SITE_ID, TD.TRANSFER_DETAIL_ID,
3 TD.LINE_NO, TD.ITEM_QTY
4 FROM TRANSFERS T, TRANSFER_DETAILS TD
5 WHERE T.BUSINESS_UNIT_ID=TD.BUSINESS_UNIT_ID 6 AND T.TRANSFER_ID=TD.TRANSFER_ID 7* AND T.TRANSFER_OCCURENCE_TYPE=TD.TRANSFER_OCCURENCE_TYPERAMSPRD>/
LINE_NO ITEM_QTY
---------------- ----------- - --------------- ----- ----- ------------------
---------- ----------
10 1 S REQUEST 10531 10198 1
1 3
10 1 S REQUEST 10531 10198 2
2 3
10 1 S REQUEST 10531 10198 3
3 3
10 1 S REQUEST 10531 10198 4
4 2
10 1 S REQUEST 10531 10198 5
5 1
10 1 S REQUEST 10531 10198 6
6 3
10 1 S REQUEST 10531 10198 7
7 1
10 1 S REQUEST 10531 10198 8
8 1
--IMA.Boundary.4953586590
Content-Type: text/plain; charset="US-ASCII"
Content-Transfer-Encoding: 7bit
Content-Description: cc:Mail note part
10 1 S REQUEST 10531 10198 9
9 3
10 1 S REQUEST 10531 10198 10
10 2
10 1 S REQUEST 10531 10198 11
11 1
10 1 S REQUEST 10531 10198 12
12 1
10 1 S REQUEST 10531 10198 13
13 1
10 1 S REQUEST 10531 10198 14
14 1
10 1 S REQUEST 10531 10198 15
15 1
10 1 S REQUEST 10531 10198 16
16 1
10 1 S REQUEST 10531 10198 17
17 2
10 1 S REQUEST 10531 10198 18
18 3
--IMA.Boundary.4953586590
Content-Type: text/plain; charset="US-ASCII"
Content-Transfer-Encoding: 7bit
Content-Description: cc:Mail note part
10 1 S REQUEST 10531 10198 19
19 1
10 1 S REQUEST 10531 10198 20
20 2
10 1 S REQUEST 10531 10198 21
21 1
10 1 S REQUEST 10531 10198 22
22 2
22 rows selected.
real: 61570
RAMSPRD>
1 SELECT /*+ RULE */ T.BUSINESS_UNIT_ID, T.TRANSFER_ID,
T.TRANSFER_OCCURENCE_TYPE,
2 T.TRANSFER_TYPE, T.FROM_SITE_ID, T.TO_SITE_ID, TD.TRANSFER_DETAIL_ID,
3 TD.LINE_NO, TD.ITEM_QTY
4 FROM TRANSFERS T, TRANSFER_DETAILS TD
5 WHERE T.BUSINESS_UNIT_ID=TD.BUSINESS_UNIT_ID
6 AND T.TRANSFER_ID=TD.TRANSFER_ID
7* AND T.TRANSFER_OCCURENCE_TYPE=TD.TRANSFER_OCCURENCE_TYPE
BUSINESS_UNIT_ID TRANSFER_ID T TRANSFER_TYPE FROM_ TO_SI TRANSFER_DETAIL_ID
LINE_NO ITEM_QTY
---------------- ----------- - --------------- ----- ----- ------------------
---------- ----------
10 1 S REQUEST 10531 10198 1
1 3
10 1 S REQUEST 10531 10198 2
2 3
10 1 S REQUEST 10531 10198 3
3 3
--IMA.Boundary.4953586590
Content-Type: text/plain; charset="US-ASCII"
Content-Transfer-Encoding: 7bit
Content-Description: cc:Mail note part
10 1 S REQUEST 10531 10198 4
4 2
10 1 S REQUEST 10531 10198 5
5 1
10 1 S REQUEST 10531 10198 6
6 3
10 1 S REQUEST 10531 10198 7
7 1
10 1 S REQUEST 10531 10198 8
8 1
10 1 S REQUEST 10531 10198 9
9 3
10 1 S REQUEST 10531 10198 10
10 2
10 1 S REQUEST 10531 10198 11
11 1
10 1 S REQUEST 10531 10198 12
12 1
10 1 S REQUEST 10531 10198 13
13 1
--IMA.Boundary.4953586590
Content-Type: text/plain; charset="US-ASCII"
Content-Transfer-Encoding: 7bit
Content-Description: cc:Mail note part
10 1 S REQUEST 10531 10198 14
14 1
10 1 S REQUEST 10531 10198 15
15 1
10 1 S REQUEST 10531 10198 16
16 1
10 1 S REQUEST 10531 10198 17
17 2
10 1 S REQUEST 10531 10198 18
18 3
10 1 S REQUEST 10531 10198 19
19 1
10 1 S REQUEST 10531 10198 20
20 2
10 1 S REQUEST 10531 10198 21
21 1
10 1 S REQUEST 10531 10198 22
22 2
22 rows selected.
real: 4070
____________________Reply Separator____________________Subject: Re: why the index is not used ? Author: ORACLE-L_at_fatcity.com
You are on the right track John. Basically there is no criteria that limits the
number of rows
returned. Your query will return every row in the two tables. It is actually
faster to do a full
tablescan than an index lookup if you are returning all the rows, so Oracle is
doing the right
thing here.
HTH,
Steve Boyd
> >FROM TRANSFERS T, TRANSFER_DETAILS TD
> >WHERE T.BUSINESS_UNIT_ID=TD.BUSINESS_UNIT_ID
> >AND T.TRANSFER_ID=TD.TRANSFER_ID
Received on Thu Apr 27 2000 - 11:35:03 CDT
 |
 |