Re: Correlated Subquery Performance Puzzle...

From: Robert Klemme <bob.news_at_gmx.net>
Date: Tue, 20 Dec 2005 13:37:37 +0100
Message-ID: <40qc8kF1bg507U1@individual.net>

mccmx_at_hotmail.com wrote:
> Oracle 9.2.0.6 on WIN 2K SP4.
>
> The following query runs for a very long time when I add an additional
> WHERE clause into the subquery block (which should effectvely limit
> the rows further....)
>
> Without the new WHERE clause the execution time is 1 minute and the
> optimizer selects a Nested Loop join followed by a FILTER via the
> subquery. With the additional WHERE clause the execution time jumps
> up to over 9 minutes and the optimizer chooses a Hash JOIN followed
> by the same FILTER via the Subquery.
>
> Why does Oracle decide to use a hash join instead of a NL join when I
> add this new WHERE clause into the subquery block...? See below for
> plans and stats:
>
> Query 1 (without additional WHERE clause)
>
> select count(*)
> from
> ps_sch_assign asg,
> ps_sch_clnd_tbl cal
> where
> asg.schedule_id = cal.schedule_id
> AND asg.effdt =
> (
> SELECT MAX(asg1.effdt)
> FROM sysadm.ps_sch_assign asg1
> WHERE asg1.emplid = asg.emplid
> AND asg1.empl_rcd = asg.empl_rcd
> )
> AND cal.schedule_id LIKE 'UK%';
>
> Elapsed: 00:01:16.04
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=58 Card=1 Bytes=36)
> 1 0 SORT (AGGREGATE)
> 2 1 FILTER
> 3 2 NESTED LOOPS (Cost=56 Card=1 Bytes=36)
> 4 3 TABLE ACCESS (FULL) OF 'PS_SCH_ASSIGN' (Cost=46 Card
> =1 Bytes=27)
>
> 5 3 INDEX (RANGE SCAN) OF 'PS_SCH_CLND_TBL' (UNIQUE) (Co
> st=10 Card=3 Bytes=27)
>
> 6 2 SORT (AGGREGATE)
> 7 6 FIRST ROW (Cost=2 Card=3 Bytes=54)
> 8 7 INDEX (RANGE SCAN (MIN/MAX)) OF 'PS_SCH_ASSIGN' (U
> NIQUE) (Cost=2 Card=18621)
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 301278 consistent gets
> 57 physical reads
> 0 redo size
> 382 bytes sent via SQL*Net to client
> 503 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
> ------------------------------------------------------------------------

---

>
> Query 2 (with additional WHERE clause)
>
> select count(*)
> from
> ps_sch_assign asg,
> ps_sch_clnd_tbl cal
> where
> asg.schedule_id = cal.schedule_id
> AND asg.effdt =
> (
> SELECT MAX(asg1.effdt)
> FROM sysadm.ps_sch_assign asg1
> WHERE asg1.emplid = asg.emplid
> AND asg1.empl_rcd = asg.empl_rcd
> AND asg.effdt <= cal.dur -- *** New WHERE clause ***
> )
> AND cal.schedule_id LIKE 'UK%';
>
> Elapsed: 00:09:35.02
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=60 Card=1 Bytes=44)
> 1 0 SORT (AGGREGATE)
> 2 1 FILTER
> 3 2 HASH JOIN (Cost=58 Card=1 Bytes=44)
> 4 3 TABLE ACCESS (FULL) OF 'PS_SCH_ASSIGN' (Cost=46 Card
> =113 Bytes=3051)
>
> 5 3 INDEX (RANGE SCAN) OF 'PS_SCH_CLND_TBL' (UNIQUE) (Co
> st=11 Card=2179 Bytes=37043)
>
> 6 2 SORT (AGGREGATE)
> 7 6 FILTER
> 8 7 FIRST ROW (Cost=2 Card=3 Bytes=54)
> 9 8 INDEX (RANGE SCAN (MIN/MAX)) OF 'PS_SCH_ASSIGN'
> (UNIQUE) (Cost=2 Card=18621)
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 33076751 consistent gets
> 270 physical reads
> 0 redo size
> 382 bytes sent via SQL*Net to client
> 503 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed

Is this maybe a typing error? I'd try

select count(*)
from
ps_sch_assign asg,
ps_sch_clnd_tbl cal
where
asg.schedule_id = cal.schedule_id

AND     asg.effdt =
           (
           SELECT MAX(asg1.effdt)
           FROM sysadm.ps_sch_assign asg1
           WHERE asg1.emplid = asg.emplid
           AND asg1.empl_rcd = asg.empl_rcd
           AND asg1.effdt <= cal.dur -- *** New WHERE clause ***
           )
AND     cal.schedule_id LIKE 'UK%';

Does it make a difference?

    robert Received on Tue Dec 20 2005 - 06:37:37 CST