Why is using Select in IN() clause much slower than using values?

From: <cliffliang_at_gmail.com>
Date: 9 Aug 2006 08:07:04 -0700
Message-ID: <1155136024.424515.226140@m79g2000cwm.googlegroups.com>

vp_alltotals returns several million rows sf_qual_paycodes contains 30 rows

This takes about 30 minutes to run:
select distinct

t.personnum,
t.applydate,
t.wfctimeinseconds as amount,

count(*)
from vp_alltotals t
where
t.applydate between t.prevpayperiodstart and t.prevpayperiodend and t.paycodename in (Select distinct name from SF_QUAL_PAYCODES) group by t.personnum, t.applydate,t.wfctimeinseconds order by t.personnum,t.applydate,t.wfctimeinseconds

This takes less than 2 minutes:
select distinct

t.personnum,
t.applydate,
t.wfctimeinseconds as amount,

count(*)
from vp_alltotals t
where
t.applydate between t.prevpayperiodstart and t.prevpayperiodend and t.paycodename in (
'Adoption','Bereavement','CME','Choice Time Payout','Compensatory
Time',
'Conference','DEOD','Holiday','Holiday Payout','Jury Duty','Long Term
Sick',
'Marriage','Orientation','Paternity','Personal Holiday','Personal
Holiday Payout',
'Salary Continuation','Sick','Sick Buy Back','Sick Long Term','Sick
NYSNA',
'Sleep Day','Staff Development','Suspension Paid','Union Meeting
External',
'Union Meeting Internal','Vacation','Vacation Payout','Workers Comp
Paid counts to OT','Workers Compensation Paid' )
group by t.personnum, t.applydate,t.wfctimeinseconds order by t.personnum,t.applydate,t.wfctimeinseconds

I had thought that Oracle would be smart enough to sense a static query and just cache the values. Is there any way I can run this fast and still use sf_qual_paycodes? I've tried an inner join but that seems to take even longer. Thanks. Received on Wed Aug 09 2006 - 10:07:04 CDT