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).
  >
  

  ---