Re: Correlated Subquery Performance Puzzle...

The Query with the additional where clause has the following extra section in the 10053 which changes the ROUNDED Cardinality from 113 to 1:

Without the additional where clause the optimzier considers EFFDT:

SINGLE TABLE ACCESS PATH
Column: EFFDT Col#: 3 Table: PS_SCH_ASSIGN Alias: SYS_ALIAS_2
    NDV: 980 NULLS: 0 DENS: 1.0204e-003 LO: 2449809 HI: 2453745

    NO HISTOGRAM: #BKT: 1 #VAL: 2
  TABLE: PS_SCH_ASSIGN ORIG CDN: 55863 ROUNDED CDN: 1 CMPTD CDN: 0
  Access path: tsc Resc: 46 Resp: 46   BEST_CST: 46.00 PATH: 2 Degree: 1

With additional where clause (AND asg.effdt <= cal.dur) the optimizer doesnt consider EFFDT:

SINGLE TABLE ACCESS PATH
  TABLE: PS_SCH_ASSIGN ORIG CDN: 55863 ROUNDED CDN: 113 CMPTD CDN: 113
  Access path: tsc Resc: 46 Resp: 46   BEST_CST: 46.00 PATH: 2 Degree: 1

The much lower ROUNDED cardinality means that when the optimizer comes to comparing join methods later, the cost of an NL join is significantly lower than the HASH join.

What I can't work out is why the optimizer now considers this new FILTER clause on EFFDT when I remove the extra WHERE clause on that same column (EFFDT) in the sub select.

Also why does oracle even care about cardinality if it has chosen to drive off that table anyway....? Surely cardinality for the driving table will be 100% of the row count when there are no filters on that table.

Matt Received on Wed Dec 21 2005 - 03:05:01 CST