Suhen, Look at what you are doing :
>Query Plan > >select orgplvee.org_lvl_parent >,prdplvee.prd_lvl_parent , > (NVL(invbalee.on_hand_qty,0)+NVL(to_intrn_qty,0)), >(NVL(invbalee.on_hand_retl,0)+NVL(to_intrn_retl,0)), >(NVL(invbalee.on_hand_cost,0)+NVL(to_intrn_cost,0)) >from > invbalee ,orgplvee ,prdplvee where >(orgplvee.org_lvl_child= > invbalee.org_lvl_child and >prdplvee.prd_lvl_child=invbalee.prd_lvl_child) > order by >orgplvee.org_lvl_parent,prdplvee.prd_lvl_parent > > >Execution Plan >Id Par Pos Ins Plan >--- ---- ---- ---- >---- > 0 #### SELECT STATEMENT (choose) >Cost >(48836,5333714,170678848) > 1 0 1 SORT (order by) Cost >(48836,5333714,170678848) > 2 1 1 HASH JOIN Cost >(1705,5333714,170678848) > 3 2 1 INDEX (analyzed) UNIQUE >JDAPROD ORGPLVEEP1 (fast >full scan) Cost (1,1073,5365) > 4 2 2 HASH JOIN Cost >(1690,1357040,36640080) > 5 4 1 INDEX (analyzed) >UNIQUE JDAPROD PRDPLVEEP1 >(fast full scan) Cost (16,100070,8005 > 6 4 2 1 TABLE ACCESS >(analyzed) JDAPROD INVBALEE >(full) Cost (746,1257164,23886116) > > >Each fetch call returned an average of 2 rows. > Your query generates a full scan of INVBALEE which looks pretty big. However, you 'feed' it no search criteria other than join conditions on ORGPLVEE and PRDPLVEE. Looks to me like a star query. Since you return few rows, the most sensible approach would probably be to use nested loops rather than the hash joins the optimizer jumps for. Summary : a) You cannot do otherwise than scan a table (or possibly an index). Pick the smallest one. b) Force the use of nested loops to get data from the other tables. I am no great fan of hints, but it looks to me like time to play around with ORDERED and USE_NL. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul INET: [EMAIL PROTECTED] 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: [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).
