Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Why is using Select in IN() clause much slower than using values?

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

From: Martin T. <bilbothebagginsbab5_at_freenet.de>
Date: 9 Aug 2006 08:40:43 -0700
Message-ID: <1155138043.736322.40530@n13g2000cwa.googlegroups.com>


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.

Which oracle version?
The Oracle SQL Reference has a description of when it will merge the IN clause. ("Unnesting of nested subqueries") Also I think the DISTINCT in the IN(..) select is not neccessary.

best,
Martin Received on Wed Aug 09 2006 - 10:40:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US