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 <[EMAIL PROTECTED]> 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: [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). > > > > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Tanel Poder > 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).
