| 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: why the index is not used ?
Colin,
Kevin's sql stmt has T.BUSINESS_UNIT_ID=TD.BUSINESS_UNIT_ID in its where clause (which refers to the first field in the primary key of table TRANSFERS), isn't is a restricting cretira?
Long
-----Original Message-----
From: shawc_at_willis.com [mailto:shawc_at_willis.com]
Sent: Thursday, April 27, 2000 10:23 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: why the index is not used ?
Kevin:
Because you haven't specified any restricting criteria (e.g. col1 = 'value1') in the predicate, the CBO has correctly decided that a sort/merge and full scan of both tables will be less costly than using the indices to access all of the rows in both tables. If you want to verify this, you could try using a hint for one or both indices and see what the estimated cost is from the new explain plan. And bear in mind that any optimizer can only *estimate* costs. Just like the estimate from your local mechanic - the final cost may be very different. But at least with Oracle you can influence the cost. That probably won't work with your mechanic.
Colin.
Please respond to ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
bcc:
Hi listers:
I've a simple parent-child relationship query:
SELECT T.BUSINESS_UNIT_ID, T.TRANSFER_ID, T.TRANSFER_OCCURENCE_TYPE,
T.TRANSFER_TYPE, T.FROM_SITE_ID, T.TO_SITE_ID, TD.TRANSFER_DETAIL_ID,
TD.LINE_NO, TD.ITEM_QTY
FROM TRANSFERS T, TRANSFER_DETAILS TD
WHERE T.BUSINESS_UNIT_ID=TD.BUSINESS_UNIT_ID AND T.TRANSFER_ID=TD.TRANSFER_ID AND T.TRANSFER_OCCURENCE_TYPE=TD.TRANSFER_OCCURENCE_TYPE;
Table TRANSFERS has PRIMARY KEY ( BUSINESS_UNIT_ID, TRANSFER_ID,
TRANSFER_OCCURENCE_TYPE )
Table TRANSFER_DETAILS has PRIMARY KEY ( BUSINESS_UNIT_ID, TRANSFER_ID,
TRANSFER_OCCURENCE_TYPE, TRANSFER_DETAIL_ID ) & referential constraint on
TRANSFERS
and the explain plans are:
MERGE JOIN (Cost=13605 Card=923364 Bytes=129270960)
SORT (JOIN)
TABLE ACCESS (FULL) OF TRANSFERS (Cost=274 Card=126961 Bytes=9268153)
SORT (JOIN)
TABLE ACCESS (FULL) OF TRANSFER_DETAILS (Cost=1524 Card=923363
Bytes=61865321)
My question is why the primary key index is not used ?
ORACLE: 7.3.4.3 HP-UX
Both tables have been analyzed (compute)?
Any insights ?
TIA
Kevin Tsay
--
Author: Kevin Tsay
INET: Kevin_Tsay_at_liz.com
The information in this email and in any attachments is confidential and may be
privileged. If you are not the intended recipient, please destroy this
message, delete any copies held on your systems and notify the sender
immediately. You should not retain, copy or use this email for any
purpose, nor disclose all or any part of its content to any other person.
--
Author:
INET: shawc_at_willis.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may Received on Thu Apr 27 2000 - 17:59:41 CDT
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |