Re: index not being used

Hi Sami

Thanks a lot it worked ..:-)

my oversight i missed comma :-)

regards
Hrishy

• Saminathan Seerangan <saminathan_at_gawab.com> wrote: >
  > Hi Hrishy
  >
  > Just try with /*+INDEX (ICWOIMP,PK_ICWOIMP) */
  > ^^ ^^
  > No space Comma
  >
  > I am not sure.
  >
  > hrishy writes:
  >
  > > Hi
  > >
  > >
  > >
  > > Oracle 8.1.7.0.0 on HP-UX 11.0
  > >
  > > We have following query .
  > > /************
  > > SELECT
  > > 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
  > > ********/
  > >
  > > Execution Plan :
  > >
  >

  ---

  
  > > SELECT STATEMENT Optimizer=CHOOSE (Cost=1178
  > > Card=1 Bytes=12
  > > 1)
  > >
  > > 0 NESTED LOOPS (Cost=1178 Card=1 Bytes=121)
  > > 1 NESTED LOOPS (Cost=1175 Card=1 Bytes=109)
  > > 2 HASH JOIN (Cost=1174 Card=1 Bytes=81)
  > > 3 TABLE ACCESS (FULL) OF
  > 'AFAS_RCPT_DTLS'
  > > (Cost=70 Car
  > > d=11603 Bytes=440914)
  > >
  > > 3 TABLE ACCESS (FULL) OF 'ICWOIMP'
  > > (Cost=830 Card=3733
  > > 9 Bytes=1605577)
  > >
  > > 2 TABLE ACCESS (BY INDEX ROWID) OF
  > > 'AFAS_RCPT_HDR' (Cost
  > > =1 Card=8343 Bytes=233604)
  > >
  > > 6 INDEX (UNIQUE SCAN) OF
  > 'PK_AFAS_RCPT_HDR'
  > > (UNIQUE)
  > > 1 TABLE ACCESS (BY INDEX ROWID) OF
  > > 'ICADDDRESSDTLS' (Cost=
  > > 3 Card=12018 Bytes=144216)
  > >
  > > 8 INDEX (RANGE SCAN) OF
  > > 'INDX_ICADDRESSDTLS_WOKEY' (NON-
  > > UNIQUE) (Cost=2 Card=12018)
  > >
  > > 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
  > >
  > > 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).
  > >
  > >
  >
  >
  >
  > Saminathan Seerangan
  >
  > ________________________________
  > Free multi-lingual web-based and POP3 email service
  > with a
  > generous 15MB of storage, a choice of themes for
  > your mailbox,
  > message filtering, plus spam and virus protection
  > Sign up now: http://www.gawab.com
  >
  > --
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.net
  > --
  > Author: Saminathan Seerangan
  > INET: saminathan_at_gawab.com
  >
  > 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).