Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: why the index is not used ?

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.

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
<!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>

<P><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">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. </FONT></P>

<P><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">If you wanna the = query uses the index, try RULE hint (It can lead to a poor = performance)</FONT>
</P>

<P><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">Hope this = helps</FONT>
</P>

<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> =
&nbsp;&nbsp;&nbsp; <FONT SIZE=3D1 FACE=3D"MS Sans = Serif">Kevin_Tsay_at_liz.com[SMTP:Kevin_Tsay_at_liz.com]</FONT> <BR><B><FONT SIZE=3D1 FACE=3D"MS Sans Serif">Responder a:</FONT></B> = &nbsp;&nbsp; <FONT SIZE=3D1 FACE=3D"MS Sans = Serif">ORACLE-L_at_fatcity.com</FONT>
<BR><B><FONT SIZE=3D1 FACE=3D"MS Sans Serif">Enviado el:</FONT></B> = &nbsp;&nbsp;&nbsp; <FONT SIZE=3D1 FACE=3D"MS Sans = Serif">Mi&eacute;rcoles 26 de Abril de 2000 17:02</FONT> <BR><B><FONT SIZE=3D1 FACE=3D"MS Sans Serif">Para:</FONT></B> &nbsp; = <FONT SIZE=3D1 FACE=3D"MS Sans Serif">Multiple recipients of list = ORACLE-L</FONT>
<BR><B><FONT SIZE=3D1 FACE=3D"MS Sans Serif">Asunto:</FONT></B> = &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D1 FACE=3D"MS = Sans Serif">why the index is not used ?</FONT> </P>

<P><FONT SIZE=3D2 FACE=3D"Arial">Hi listers:</FONT> </P>

<P><FONT SIZE=3D2 FACE=3D"Arial">I've a simple parent-child = relationship query:</FONT>
</P>

<P><FONT SIZE=3D2 FACE=3D"Arial">SELECT T.BUSINESS_UNIT_ID, = T.TRANSFER_ID, T.TRANSFER_OCCURENCE_TYPE,</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">T.TRANSFER_TYPE, T.FROM_SITE_ID, = T.TO_SITE_ID, TD.TRANSFER_DETAIL_ID,</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">TD.LINE_NO, TD.ITEM_QTY</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">FROM TRANSFERS T, TRANSFER_DETAILS = TD</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">WHERE = T.BUSINESS_UNIT_ID=3DTD.BUSINESS_UNIT_ID</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">AND&nbsp;&nbsp; = T.TRANSFER_ID=3DTD.TRANSFER_ID</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">AND&nbsp;&nbsp; = T.TRANSFER_OCCURENCE_TYPE=3DTD.TRANSFER_OCCURENCE_TYPE;</FONT> </P>

<P><FONT SIZE=3D2 FACE=3D"Arial">Table TRANSFERS has PRIMARY KEY ( = BUSINESS_UNIT_ID, TRANSFER_ID,</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">TRANSFER_OCCURENCE_TYPE ) </FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">Table TRANSFER_DETAILS has PRIMARY = KEY ( BUSINESS_UNIT_ID, TRANSFER_ID,</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">TRANSFER_OCCURENCE_TYPE, = TRANSFER_DETAIL_ID ) &amp; referential constraint on</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">TRANSFERS</FONT> </P>

<P><FONT SIZE=3D2 FACE=3D"Arial">and the explain plans are:</FONT> <BR><FONT SIZE=3D2 =

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

<BR><FONT SIZE=3D2 FACE=3D"Arial">SELECT STATEMENT Optimizer=3DCHOOSE = (Cost=3D13605 Card=3D923364 Bytes=3D129270960)</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">&nbsp;&nbsp;&nbsp; MERGE JOIN = (Cost=3D13605 Card=3D923364 Bytes=3D129270960)</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">&nbsp;&nbsp;&nbsp; SORT (JOIN)</FONT> <BR><FONT SIZE=3D2 =
FACE=3D"Arial">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TABLE ACCESS = (FULL) OF TRANSFERS (Cost=3D274 Card=3D126961 Bytes=3D9268153)</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">&nbsp;&nbsp;&nbsp; SORT (JOIN)</FONT> <BR><FONT SIZE=3D2 =
FACE=3D"Arial">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TABLE ACCESS = (FULL) OF TRANSFER_DETAILS (Cost=3D1524 Card=3D923363</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">Bytes=3D61865321)</FONT> <BR><FONT SIZE=3D2 =
FACE=3D"Arial">---------------------------------------------------------=
-----------------</FONT>

</P>

<P><FONT SIZE=3D2 FACE=3D"Arial">My question is why the primary key = index is not used ?</FONT>
</P>

<P><FONT SIZE=3D2 FACE=3D"Arial">ORACLE: 7.3.4.3 HP-UX</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">Both tables have been analyzed = (compute)?</FONT>
</P>

<P><FONT SIZE=3D2 FACE=3D"Arial">Any insights ?</FONT> </P>

<P><FONT SIZE=3D2 FACE=3D"Arial">TIA</FONT> </P>

<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</FONT>
</P>

<P><FONT SIZE=3D2 FACE=3D"Arial">Fat City Network = Services&nbsp;&nbsp;&nbsp; -- (858) 538-5051&nbsp; FAX: (858) = 538-5051</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">San Diego, = California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet = access / Mailing Lists</FONT>
<BR><FONT SIZE=3D2 =

FACE=3D"Arial">---------------------------------------------------------=
-----------</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">To REMOVE yourself from this mailing = list, send an E-Mail message</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">to: ListGuru_at_fatcity.com (note EXACT = spelling of 'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">the message BODY, include a line = containing: UNSUB ORACLE-L</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">(or the name of mailing list you want = Received on Wed Apr 26 2000 - 15:08:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US