Re: Correlated Subquery Performance Puzzle...

Good observation skills....

That clause is wrong. However if I change it to:

AND asg1.effdt < = cal.dur

it is even worse - execution time is over 12 minutes....!

Elapsed: 00:12:59.06

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         FIRST ROW (Cost=2 Card=1 Bytes=18)
   8    7           INDEX (RANGE SCAN (MIN/MAX)) OF 'PS_SCH_ASSIGN' (U
          NIQUE) (Cost=2 Card=55863)

Statistics

          0  recursive calls
          0  db block gets
   59778142  consistent gets
        450  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