Message-Id: <10479.104228@fatcity.com> From: "Alvarez, Carlos" Date: Wed, 26 Apr 2000 17:08:44 -0300 Subject: RE: why the index is not used ? This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------ =_NextPart_001_01BFAFBB.399118BE Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I think that, based on the statistics, the CBO is 'assuming' that the query's result will be a great percentage of rows, and then its better to do a full table scan.=20 If you wanna the query uses the index, try RULE hint (It can lead to a poor performance) Hope this helps Carlos > ---------- > De: Kevin_Tsay@liz.com[SMTP:Kevin_Tsay@liz.com] > Responder a: ORACLE-L@fatcity.com > Enviado el: Mi=E9rcoles 26 de Abril de 2000 17:02 > Para: Multiple recipients of list ORACLE-L > Asunto: why the index is not used ? >=20 > Hi listers: >=20 > I've a simple parent-child relationship query: >=20 > 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=3DTD.BUSINESS_UNIT_ID > AND T.TRANSFER_ID=3DTD.TRANSFER_ID > AND T.TRANSFER_OCCURENCE_TYPE=3DTD.TRANSFER_OCCURENCE_TYPE; >=20 > Table TRANSFERS has PRIMARY KEY ( BUSINESS_UNIT_ID, TRANSFER_ID, > TRANSFER_OCCURENCE_TYPE )=20 > Table TRANSFER_DETAILS has PRIMARY KEY ( BUSINESS_UNIT_ID, > TRANSFER_ID, > TRANSFER_OCCURENCE_TYPE, TRANSFER_DETAIL_ID ) & referential = constraint > on > TRANSFERS >=20 > and the explain plans are: > = ---------------------------------------------------------------------- > ---- > SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D13605 Card=3D923364 > Bytes=3D129270960) > MERGE JOIN (Cost=3D13605 Card=3D923364 Bytes=3D129270960) > SORT (JOIN) > TABLE ACCESS (FULL) OF TRANSFERS (Cost=3D274 Card=3D126961 > Bytes=3D9268153) > SORT (JOIN) > TABLE ACCESS (FULL) OF TRANSFER_DETAILS (Cost=3D1524 = Card=3D923363 > Bytes=3D61865321) > = ---------------------------------------------------------------------- > ---- >=20 > My question is why the primary key index is not used ? >=20 > ORACLE: 7.3.4.3 HP-UX > Both tables have been analyzed (compute)? >=20 > Any insights ? >=20 > TIA >=20 > Kevin Tsay > --=20 > Author: Kevin Tsay > INET: Kevin_Tsay@liz.com >=20 > 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@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). >=20 >=20 ------ =_NextPart_001_01BFAFBB.399118BE Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: why the index is not used ?

I think that, based = on the statistics, the CBO is 'assuming' that the query's result will = be a great percentage of rows, and then its better to do a full table = scan.

If you wanna the = query uses the index, try RULE hint (It can lead to a poor = performance)

Hope this = helps

Carlos