Calculating IX_SEL in 10053.

From: <mccmx_at_hotmail.com>
Date: 5 Jan 2007 04:12:01 -0800
Message-ID: <1167999121.358583.77300@42g2000cwt.googlegroups.com>

Oracle 9.2.0.6 on Windows.

I'm having trouble interpreting the SINGLE TABLE ACCESS PATH section of a simple 10053 trace file.

According to Jonathan Lewis' CBO book (Chapter 14, Page 414), the selectivity values are calculated only from filter conditions not join conditions.

But I am having trouble determining how the optimizer has derived the IX_SEL, and TB_SEL values below.

Query:

explain plan for
UPDATE PS_TY_MF_TIM_EMP EMP
SET TY_YTD_TL_QTY_3 = nvl
(
(

SELECT SUM( PAY.TL_QUANTITY ) FROM PS_TL_PAYABLE_TIME PAY WHERE
   EMP.EMPLID = PAY.EMPLID

AND        EMP.EMPL_RCD = PAY.EMPL_RCD
AND        PAY.DUR >= TRUNC(TO_DATE('27-NOV-06','DD-MON-YY'),'Y')
AND        PAY.DUR <= TO_DATE('24-DEC-06','DD-MON-YY')
AND        PAY.TRC = 'C169'

), 0
)
WHERE EMP.PROCESS_INSTANCE = 1145447; 10053 Trace:

/* Snipped.....

SINGLE TABLE ACCESS PATH

Column:     EMPLID  Col#: 1      Table: PS_TL_PAYABLE_TIME   Alias: PAY
    NDV: 15660     NULLS: 0         DENS: 6.3857e-005
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column:   EMPL_RCD  Col#: 2      Table: PS_TL_PAYABLE_TIME   Alias: PAY
    NDV: 1         NULLS: 0         DENS: 1.0000e+000 LO:  0  HI: 0
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column:        TRC  Col#: 7      Table: PS_TL_PAYABLE_TIME   Alias: PAY
    NDV: 218       NULLS: 0         DENS: 4.5872e-003
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column:        DUR  Col#: 3      Table: PS_TL_PAYABLE_TIME   Alias: PAY
    NDV: 1409      NULLS: 0         DENS: 7.0972e-004 LO:  2453006  HI:

2454414

    NO HISTOGRAM: #BKT: 1 #VAL: 2
  TABLE: PS_TL_PAYABLE_TIME ORIG CDN: 25408251 ROUNDED CDN: 1 CMPTD CDN: 0
  Access path: tsc Resc: 106979 Resp: 106979   Access path: index (scan)

      Index: PSCTL_PAYABLE_TIME
  TABLE: PS_TL_PAYABLE_TIME
      RSC_CPU: 0 RSC_IO: 5
  IX_SEL: 1.4646e-008 TB_SEL: 1.4646e-008   Access path: index (scan)

      Index: PSDTL_PAYABLE_TIME
  TABLE: PS_TL_PAYABLE_TIME
      RSC_CPU: 0 RSC_IO: 9822
  IX_SEL: 5.0000e-002 TB_SEL: 2.2936e-004   Access path: index (scan)

      Index: PS_TL_PAYABLE_TIME
  TABLE: PS_TL_PAYABLE_TIME
      RSC_CPU: 0 RSC_IO: 26
  IX_SEL: 3.1928e-006 TB_SEL: 3.1928e-006   BEST_CST: 5.00 PATH: 4 Degree: 1

....Snipped */

PSCTL_PAYABLE_TIME is an index on (EMPLID,TRC,DUR).

Since the EMPLID is not a filter condition (only a join condition) I would expect the IX_SEL to be 1 because the leading column isn't considered, and the TB_SEL to be a product of the selectivity of the 2 columns which are used as filter conditions on PS_TL_PAYABLE_TIME:

4.5872e-003 * 7.0972e-004 = 0.000003255 (3.255e-006).

The numbers don't add up - anyone know where my understanding is wrong here....

Thanks for you help...

Matt Received on Fri Jan 05 2007 - 06:12:01 CST