tuning a query

[prasen999]

hi folks,

i have a query as below:-


select r.parentid parentid,r.id ownid,sum(a.amount)/10000000 rcptcuryr, 0 rcptlstyr
from rcpt_rep_master r,rcpt_rep_link b,rcpt_2002 a
where substr(a.majorhead||a.SUBMAJORHEAD||a.MINORHEAD||a .scheme||a.GRPSUBHEAD||a.subhead,b.start_len,b.end _len) between trim(b.from_sh) and trim(b.to_sh)
and a.rcpdate between '01-sep-2003' and '30-oct-2003'
and b.add_sub = 'A'
and r.goi = 'N'
and r.id = b.id
group by r.parentid,r.id



the database is maintained by someone else, so plzz i have nothing to do with such messy table structure...plzz bear.

rcpt_2002 doesnt have ne index.
rcpt_rep_master,rcpt_rep_link r snapshots in my databse.( i have created composite function based index on trim(b.from_sh),trim(b.to_sh) in the remote databse).


the execution time of this query is 6secs.
the plan is attached.

can u plzz help me tune this.

thanks and regards,
Prasen