Re: why the index is not used ?

>how many rows in the tables?

Table cardinality is shown in the explain plan:

> TABLE ACCESS (FULL) OF TRANSFERS (Cost=274 Card=126961 Bytes=9268153)
> SORT (JOIN)
> TABLE ACCESS (FULL) OF TRANSFER_DETAILS (Cost=1524 Card=923363

Colin.

Please respond to ORACLE-L_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:
bcc:

Subject: Re: why the index is not used ?

how many rows in the tables?

At 12:02 PM 4/26/00 -0800, Kevin Tsay wrote:
>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:
>--------------------------------------------------------------------------
>SELECT STATEMENT Optimizer=CHOOSE (Cost=13605 Card=923364 Bytes=129270960)
> 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.