RE: why the index is not used ?

From: Alvarez, Carlos <CAlvarez_at_uniFON.com.ar>
Date: Wed, 26 Apr 2000 17:08:44 -0300
Message-Id: <10479.104228@fatcity.com>

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_at_liz.com[SMTP:Kevin_Tsay_at_liz.com]
> Responder a: ORACLE-L_at_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_at_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_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).
>=20
>=20

=_NextPart_001_01BFAFBB.399118BE Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =

charset=3DUS-ASCII">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.1960.3">

<TITLE>RE: why the index is not used ?</TITLE>
</HEAD>
<BODY>

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

<P><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">Carlos</FONT>
</P>
<UL>
<P><FONT SIZE=3D1 FACE=3D"MS Sans Serif">----------</FONT>
<BR><B><FONT SIZE=3D1 FACE=3D"MS Sans Serif">De:</FONT></B> =

Kevin_Tsay_at_liz.com[SMTP:Kevin_Tsay_at_liz.com] Responder a: =    ORACLE-L_at_fatcity.com
 Enviado el: =     Miércoles 26 de Abril de 2000 17:02 Para:   = Multiple recipients of list = ORACLE-L
 Asunto: =         why the index is not used ?

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=3DTD.BUSINESS_UNIT_ID AND   = T.TRANSFER_ID=3DTD.TRANSFER_ID
 AND   = T.TRANSFER_OCCURENCE_TYPE=3DTD.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: <FONT SIZE=3D2 =

FACE=3D"Arial">---------------------------------------------------------=
-----------------</FONT>

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) <FONT SIZE=3D2 =

FACE=3D"Arial">---------------------------------------------------------=
-----------------</FONT>

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

<P><FONT SIZE=3D2 FACE=3D"Arial">Kevin Tsay</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">-- </FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Author: Kevin Tsay</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&nbsp; 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
 <FONT SIZE=3D2 =

FACE=3D"Arial">---------------------------------------------------------=

-----------
 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 = Received on Wed Apr 26 2000 - 15:08:44 CDT