Re: index skip scan

From: jernigam_at_nospam.kochind.com <jernigam_at_kochind.com>
Date: Tue, 6 Jan 2009 08:57:04 -0800 (PST)
Message-ID: <225347a4-0a86-4bef-af3c-0bd4cc5df802_at_t26g2000prh.googlegroups.com>

It was level 1

  PARAMETERS WITH ALTERED VALUES

---

  hash_area_size                      = 2097152
  sort_area_size                      = 1048576
  optimizer_index_cost_adj            = 50
   query_rewrite_enabled               = false
  optimizer_secure_view_merging       = false
  *********************************

Table Stats::
  Table: INVENTORY_TRANS_QTY Alias: ITQ     #Rows: 13991744 #Blks: 337461 AvgRowLen: 158.00 Index Stats::
  Index: INV_TRANS_QTY_IND2 Col#: 10
    LVLS: 2 #LB: 33244 #DK: 4022 LB/K: 8.00 DB/K: 438.00 CLUF: 1765553.00
  Index: INV_TRANS_QTY_IND3 Col#: 4 10
    LVLS: 2 #LB: 29916 #DK: 865907 LB/K: 1.00 DB/K: 3.00 CLUF: 2607782.00

   Access Path: index (RangeScan)
    Index: INV_TRANS_QTY_IND2
    resc_io: 1435.00 resc_cpu: 23193396     ix_sel: 7.9620e-04 ix_sel_with_filters: 7.9620e-04     Cost: 726.76 Resp: 726.76 Degree: 1

  Access Path: index (SkipScan)
    Index: INV_TRANS_QTY_IND3
    resc_io: 987.00 resc_cpu: 9550211
    ix_sel: 1.6282e-04 ix_sel_with_filters: 1.6282e-04     Cost: 497.31 Resp: 497.31 Degree: 1

Predicate Information:

---

2 - access("ITQ"."EFFECTIVE_DATE">=TO_DATE('2008-12-01 00:00:00',
'yyyy-mm-dd hh24:mi:ss') AND "ITQ"."EFFECTIVE_DATE"<TO_DATE
('2008-12-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) 2 - filter(("ITQ"."EFFECTIVE_DATE">=TO_DATE('2008-12-01 00:00:00',
'yyyy-mm-dd hh24:mi:ss') AND "ITQ"."EFFECTIVE_DATE"<TO_DATE
('2008-12-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss')))

Content of other_xml column

---

  db_version     : 10.2.0.3
  parse_schema   : MJERNDBA
  plan_hash      : 1361310

  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      OPT_PARAM('query_rewrite_enabled' 'false')
      OPT_PARAM('optimizer_index_cost_adj' 50)
      ALL_ROWS
      OUTLINE_LEAF(_at_"SEL$1")
      INDEX_SS(_at_"SEL$1" "ITQ"@"SEL$1" ("INVENTORY_TRANS_QTY"."ADDR_ID"

"INVENTORY_TRANS_QTY"."EFFECTIVE_DATE"))     END_OUTLINE_DATA
  */ Received on Tue Jan 06 2009 - 10:57:04 CST