Correlated Subquery Performance Puzzle...

From: <mccmx_at_hotmail.com>
Date: 20 Dec 2005 04:24:07 -0800
Message-ID: <1135081447.578167.222460@g47g2000cwa.googlegroups.com>

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

Received on Tue Dec 20 2005 - 06:24:07 CST