quote:

---

Originally posted by Bustaz Kool

A few thoughts/questions off the top of my head...
1) Is there a reason that you are performing the subquery on ACCOUNTS before joining the other tables?
2) Have you tried putting the additional filtering (Dates and IB_cd) inside the subquery?
3) What indexes are on the two date columns?
4) What does the execution plan look like?

My (blind) attempt at re-writing the query:[CODE]select
a.acct_nbr,
a.REGVALUE,
a.Addr1,
a.Addr2,
a.Addr3,
a.Addr4,
a.city,
a.state_id,
a.zip
from
ACCOUNT a
inner join
FIRM f
on a.tech_id = f.tech_id
inner join
PROD p
on a.prod_cd = p.prod_cd
where
(
UPPER(A.REGVALUE) LIKE '% 3150IAC %'
or UPPER(A.REGVALUE) LIKE '% 3150 IAC %'
or UPPER(A.REGVALUE) LIKE '% 3150 (IAC) %'
or UPPER(A.REGVALUE) LIKE '% 3150_IAC %'
or UPPER(A.REGVALUE) LIKE '% WARD %'
)
and (UPPER(A.REGVALUE) NOT LIKE '% JTWROS %'
and UPPER(A.REGVALUE) NOT LIKE '% BOTHYACK %'
and UPPER(A.REGVALUE) NOT LIKE '% MYCOLLECTION %')
and (
a.est_dt between '01-JAN11' and '31-DEC-11'
or
a.closed_dt between '01-JAN-11' and '31-DEC-11'
)
and a.IB_cd in ('EXN', 'DTS', 'ALP')[/CODE]

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

---