From bruce.taneja@mcd.com Fri, 16 Mar 2001 11:55:46 -0800 From: bruce.taneja@mcd.com Date: Fri, 16 Mar 2001 11:55:46 -0800 Subject: RE: HINTS , how to add correctly Message-ID: MIME-Version: 1.0 Content-Type: text/plain Yes, I did add statistics, though 'estimated' one only.. thanks, Bruce "Khedr, Waleed" To: Multiple recipients of list ORACLE-L @FMR.COM> cc: Sent by: Subject: RE: HINTS , how to add correctly root@fatcity. com 03/15/2001 06:20 PM Please respond to ORACLE-L Did you update the statistics for these tables? -----Original Message----- Sent: Thursday, March 15, 2001 6:20 PM To: Multiple recipients of list ORACLE-L My query runs fine (5 sec) under rule based optimizer but takes 1.5 minutes for cost based.. Our company wants to use only cost based optimizer and does not want to use the *+rule* hint.. So I thought I can use the hints as suggested by the explain plan of rule based optimizer.. In specific the cost based optimizer used nested loops instead of merge join.. and using hints does not make it switch back to using merge join.. Any help would be a life saver! RULE plan from the Fast 5 second execution ======================================== Operation Name SELECT STATEMENT SORT ORDER BY MERGE JOIN OUTER SORT JOIN MERGE JOIN OUTER SORT JOIN TABLE ACCESS BY INDE TBRPTTB INDEX RANGE SCAN I1RPTTB SORT JOIN VIEW R1STRCUS FILTER TABLE ACCESS FULL TBSTRCUS NESTED LOOPS INDEX RANGE SCAN I1USRRGN TABLE ACCESS BY ITBCCSITE INDEX RANGE SCAN I1CCSITE SORT JOIN VIEW R1CUSGRP FILTER TABLE ACCESS FULL TBCUSGRP INDEX UNIQUE SCAN I1USRRGN the SLOW/ COST based plan - 1.5 plan.. (hint making no diiference!) ======================================= Operation Name Rows Bytes Cost SELECT STATEMENT 1 1K 40 SORT ORDER BY 1 1K 40 NESTED LOOPS OUTER 1 1K 38 NESTED LOOPS OUTER 1 1K 37 TABLE ACCESS BY INDEX TBRPTTB 1 1K 1 INDEX RANGE SCAN I1RPTTB 1 1 VIEW B1STRCUS 101 1K HASH JOIN 101 4K 36 NESTED LOOPS 477 15K 32 INDEX RANGE SCAN I1USRRGN 2 24 2 TABLE ACCESS BY IND TBCCSITE 53K 1M 16 INDEX RANGE SCAN I2CCSITE 53K 20 TABLE ACCESS FULL TBSTRCUS 11K 88K 2 VIEW R1CUSGRP 18 468 FILTER TABLE ACCESS FULL TBCUSGRP 18 396 1 INDEX UNIQUE SCAN I1USRRGN 1 12 1 Adding Hints to query making no difference.. I guess this is where I need help! how to add hints correctly! select /*+ USE_MERGE(TBRPTTB,B1STRCUS) */ rept_clmn_11_ds reg_cd, rept_clmn_27_ds reg_na, rept_clmn_15_ds owsh_sort_rept_cd, rept_clmn_16_ds|| ' STORES' OWNER, rept_clmn_08_ds OPERATOR, rept_clmn_01_qt site_id_nu, substr(rept_clmn_01_ds,1,16) ADDRESS, rept_clmn_01_dt str_open_dt, decode(rept_clmn_05_qt,null,0,rept_clmn_05_qt) act_mon_sls_am, decode(rept_clmn_10_qt,null,0,rept_clmn_10_qt) act_mon_sls_am, (rept_clmn_05_qt - rept_clmn_10_qt) DIFF, ((rept_clmn_05_qt - rept_clmn_10_qt) / decode(rept_clmn_10_qt,0,decode(rept_clmn_05_qt,0,1,rept_clmn_05_qt), rept_clmn_10_qt))*100 DIFF1, decode(rept_clmn_06_qt,null,0,rept_clmn_06_qt) act_mon_cust_am, decode(rept_clmn_11_qt,null,0,rept_clmn_11_qt) act_mon_cust_am, decode(rept_clmn_14_qt,null,0,rept_clmn_14_qt) drv_thu_trn_qt, decode(rept_clmn_15_qt,null,0,rept_clmn_15_qt) brkf_trn_qt, ( rept_clmn_06_qt - rept_clmn_11_qt ) DIFF2, (( rept_clmn_06_qt - rept_clmn_11_qt) /decode(rept_clmn_11_qt,0,decode(rept_clmn_06_qt,0,1,rept_clmn_06_qt), rept_clmn_11_qt))*100 DIFF3, rept_clmn_08_qt drv_thu, rept_clmn_09_qt brkf_sls, decode(rept_clmn_26_ds,null,null,'*') comp_fl, decode(rept_clmn_05_qt,0,'*',null) no_curr_sls_fl, decode(b.cusm_sort_grp_ds,null,'NONE',b.cusm_sort_grp_ds) cusm_sort_grp_ds, decode(c.cusm_sort_grp_cd,null,'0',c.cusm_sort_grp_cd)cusm_sort_grp_cd from inf_v1rpttb a, ste_r1cusgrp b, ste.b1strcus c where rept_clmn_01_qt = c.site_id_nu(+) and c.cusm_sort_grp_cd = b.cusm_sort_grp_cd(+) and rept_seq_nu = 353000 and rept_clmn_01_dt <= '2/1/2001' and rept_clmn_28_ds = 'PRIMARY' order by rept_clmn_11_ds, c.cusm_sort_grp_cd, rept_clmn_15_ds, rept_clmn_08_ds, rept_clmn_01_qt thanks much in advance!! Bruce Taneja -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: bruce.taneja@mcd.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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.com -- Author: Khedr, Waleed INET: Waleed.Khedr@FMR.COM Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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.com -- Author: INET: bruce.taneja@mcd.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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).