Message-Id: <24748.322793@fatcity.com> From: Stephane Faroult Date: Sat, 22 Mar 2003 17:43:39 +0100 Subject: Re: Query Tuning urgent manoj.gurnani@orbitech.co.in 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.