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

cliffliang_at_gmail.com wrote:
> 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.

What's Oracle version? Is there any difference in the plans for the two queries (I suppose there is?) Without seeing the plans it's hard to tell where the CBO takes the wrong turn... If statistics are not current, you may want to gather them, with histograms. If it doesn't help, you may want to hint the query with IN(SELECT...) so that the plan is the same as for the query with literals.

The CBO is indeed smart, but it doesn't know the data as good as you do. After all, it's just a program, though a very sophisticated one... So if it fails to figure out the best access paths given all possible information (current stats, histograms, all other factors that affect its calculations,) you can always hint it to execute the query the way you know it should.

Hth,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Wed Aug 09 2006 - 11:15:50 CDT