Hi Steve,
I think in this case since table TRANSFER_DETAILS has a referential constraint on table TRANSEFRS the no. of rows returned from the query would be the same as the no. of rows in TRANSFER_DETAILS (which may be different to the no. of rows in table TRANSFERS). Am I correct here?
One question: what are the Card and Bytes field in the EXPLAIN PLAN indicate?
Long
-----Original Message-----
From: Steve Boyd [mailto:pimaco_oracle_at_yahoo.com]
Sent: Thursday, April 27, 2000 11:29 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: why the index is not used ?
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
- John Barron <jbarron_at_windriver.com> wrote:
> 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
> >
> >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> >San Diego, California -- Public Internet access / Mailing Lists
> >--------------------------------------------------------------------
> >To REMOVE yourself from this mailing list, send an E-Mail message
> >to: 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
> >also send the HELP command for other information (like subscribing).
>
> --
> Author: John Barron
> INET: jbarron_at_windriver.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: 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
> also send the HELP command for other information (like subscribing).
Do You Yahoo!?
Talk to your friends online and get email alerts with Yahoo! Messenger.
http://im.yahoo.com/
--
Author: Steve Boyd
INET: pimaco_oracle_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: 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 - 18:17:17 CDT
