[EMAIL PROTECTED] wrote: > > The following qry takes large amt of time to retrieve data on production > database. > > Reason being for a single row in psd, there are multiple records in > piar_fr_psd representing diff parties. > > How to optimise this qry . > > Select CUST_BAS_NO,BR_COD,CUST_NAM,BR_NAM from ( > > select > > distinct(decode(b.sys_id,'TRDENG',rtrim(a.cust_bas_no),rtrim(a.cosmos_ba > se_no))) cust_bas_no , > > a.br_cod br_cod ,pty_nam cust_nam,bank_name br_nam, > > rank() over (partition by > > decode(b.sys_id,'TRDENG',rtrim(a.cust_bas_no),rtrim(a.cosmos_base_no)),a > .br_cod > > order by pty_nam) as rk > > from piar_fr_psd a, psd b, bank_br c > > where a.psd_id=b.psd_id > > and a.psd_serial_num = b.psd_serial_no > > and b.bank_id = c.bank_id > > and (((sys_id = 'TRDENG') and (a.cust_bas_no is not null)) > > or ((sys_id <> 'TRDENG') and (a.cosmos_base_no is not null))) > > ) > > where rk = 1 > > order by br_cod,cust_bas_no; > > > > Thanks > > Manoj
Manoj, Very quickly your DISTINCT seems totally useless since you have your 'where rk = 1' condition to ensure you return a single row. Your join with bank_br could be done at the highest (least nested) level. I note that, bar the bank name, everything comes from PIAR_FR_PSD. When you say that there are multiple records for each row in PSD, does it mean that occasionally you can have two or three rows, or that you generally have dozens of rows? I think that in the first case DISTINCT might prove to be better than the analytic function, especially since you have an ORDER BY anyway. In the second case, the analytic function is probably by far the best solution. But as said above, having both seems redundant. A bit awkward to use a column from one table to interpret a table from another; design doesn't seem to be flawless. -- HTH, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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).
