Hi Sami Thanks a lot it worked ..:-)
my oversight i missed comma :-) regards Hrishy --- Saminathan Seerangan <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
