Re: index not being used

Hi!

Let's start with that: where is your problem?

Do you have a performance problem with current execution plan or do you just want to see indexes used?
Execution plans might change if you add those indexes, just try and see. It could happen that hash join of A and D tables are replaced with nested loops, with D being driving table...

Tanel.

• Original Message ----- From: "hrishy" <hrishys_at_yahoo.co.uk> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, July 25, 2003 1:04 PM Subject: Re: index not being used

> Hi Taner
>
> So do you suggest that i create a index on CANNO or
> WONO feilds and experiment.
>
>
> D.DOCLINKREFNUM = A.CANNO
> or
> D.DOCLINKNUM = A.WONO
>
> regards
> Hrishy
>
>
> --- Tanel Poder <tanel.poder.003_at_mail.ee> wrote: >
> Hi!
> >
> > > This plan shows that ICWOIMP is accessed FULL .
> > > Actually this table
> > > has a Primary Key on WOKEY & this is used in JOIN
> > > condition . WHy is
> > > it not using that index
> >
> > But you also have two joins between ICWOIMP (A) and
> > AFAS_RCPT_DTLS (D)
> > tables in your query: CBO finds that it's better to
> > hash join those two, and
> > it's better to use multiblock reads from table to
> > get the data for join
> > either because of cost or because you do not have
> > indexes / other predicates
> > on those columns.
> >
> > And since you already have read whole ICWOIMP table
> > due CBO decided join
> > order, there's no point of reading any more data
> > from it, thus no index is
> > required and the hint is ignored. Instead the hash
> > joined result set is
> > taken and is joined to rest of tables using nested
> > loops.
> >
> > You have to hint join orders if you definitely want
> > to get join using WOKEY
> > happen first.
> >
> > Tanel.
> >
> > >
> > > I tried to force this index
> > >
> > > SELECT /*+ INDEX (ICWOIMP PK_ICWOIMP) */
> > > A.CANNO "INVOICE NO",
> > > A.CANDATE "INVOICE DATE",
> > > --B.NAME "CUSTOMER NAME",
> > > A.CANAMT "REVENUE AMOUNT",
> > > C.RCPTDOCNO "RECEIPT NO.",
> > > D.RECEIPTAMOUNT "RECEIPT AMOUNT",
> > > C.RCPTDATE "RECEIPT DATE"
> > > FROM
> > > ICWOIMP A,
> > > ICADDDRESSDTLS B,
> > > AFAS_RCPT_HDR C,
> > > AFAS_RCPT_DTLS D
> > > WHERE
> > > A.WOKEY = B.WOKEY
> > > AND D.RECEIPTHDRID = C.RECEIPTHDRID
> > > AND ADDTYPE ='SHPR'
> > > --AFAS_RCPT_DTLS.RECEIPTHDRID =
> > > AFAS_RCPT_HDR.RECEIPTHDRID
> > > AND D.DOCLINKREFNUM = A.CANNO
> > > AND D.DOCLINKNUM = A.WONO
> > >
> > > But still with this , execution plan remained the
> > > same.
> > > AM I missing something ? Can Oracle ignore the
> > hint
> > > although provided
> > > ?
> > >
> > > P.S. Statistics are Up-To-Date for all tables.
> > >
> > > can anybody tell me why my hinet is being ignored
> > >
> > >
> >
> ________________________________________________________________________
> > > Want to chat instantly with your online friends?
> > Get the FREE Yahoo!
> > > Messenger http://uk.messenger.yahoo.com/
> > > --
> > > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.net
> > > --
> > > Author: =?iso-8859-1?q?hrishy?=
> > > INET: hrishys_at_yahoo.co.uk
> > >
> > > Fat City Network Services -- 858-538-5051
> > http://www.fatcity.com
> > > San Diego, California -- Mailing list and
> > web hosting services
> > >
> >
> ---------------------------------------------------------------------
> > > 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).
> > >
> > >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.net
> > --
> > Author: Tanel Poder
> > INET: tanel.poder.003_at_mail.ee
> >
> > Fat City Network Services -- 858-538-5051
> > http://www.fatcity.com
> > San Diego, California -- Mailing list and web
> > hosting services
> >
> ---------------------------------------------------------------------
> > 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).
> >
>
> ________________________________________________________________________
> Want to chat instantly with your online friends? Get the FREE Yahoo!
> Messenger http://uk.messenger.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: =?iso-8859-1?q?hrishy?=
> INET: hrishys_at_yahoo.co.uk
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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
Received on Fri Jul 25 2003 - 06:28:13 CDT