Re: 10053 Trace File anomolies

From: <mccmx_at_hotmail.com>
Date: 27 Jun 2005 06:50:09 -0700
Message-ID: <1119880209.031744.189970@g44g2000cwa.googlegroups.com>

The optimizer seems to choose the FTS over the Index Skip Scan despite the fact that the latter had a much lower cost of 831 (and the FTS cost was 2297):

BEST_CST: 2297.00 PATH: 2 Degree: 1

SINGLE TABLE ACCESS PATH
Column: DUR Col#: 2 Table: PS_TY_SCH_CLND_TBL Alias: SYS_ALIAS_14
NDV: 7686 NULLS: 0 DENS: 1.3011e-004 LO: 2447893 HI: 2455578

    NO HISTOGRAM: #BKT: 1 #VAL: 2
  TABLE: PS_TY_SCH_CLND_TBL ORIG CDN: 1812808 ROUNDED CDN: 1887 CMPTD CDN: 1887
  Access path: tsc Resc: 2297 Resp: 2297   Skip scan: ss-sel 0 andv 831
    ss cost 831
    table io scan cost 2297
  Skip scan chosen
  Access path: index (no sta/stp keys)

      Index: PS0TY_SCH_CLND_TBL
  TABLE: PS_TY_SCH_CLND_TBL
      RSC_CPU: 0 RSC_IO: 14936
  IX_SEL: 2.7592e-004 TB_SEL: 1.9508e-001   Skip scan: ss-sel 0 andv 831
    ss cost 831
    table io scan cost 2297
  Skip scan chosen
  Access path: index (no sta/stp keys)

      Index: PS_TY_SCH_CLND_TBL
  TABLE: PS_TY_SCH_CLND_TBL
      RSC_CPU: 0 RSC_IO: 4051
  IX_SEL: 2.7592e-004 TB_SEL: 1.9508e-001   Access path: index (no sta/stp keys)

      Index: PS0TY_SCH_CLND_TBL
  TABLE: PS_TY_SCH_CLND_TBL
      RSC_CPU: 0 RSC_IO: 8932
  IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000   Access path: index (no sta/stp keys)

      Index: PS_TY_SCH_CLND_TBL
  TABLE: PS_TY_SCH_CLND_TBL
      RSC_CPU: 0 RSC_IO: 6980
  IX_SEL: 1.0000e+000 TB_SEL: 1.0000e+000   BEST_CST: 2297.00 PATH: 2 Degree: 1 Received on Mon Jun 27 2005 - 08:50:09 CDT