RE: Index Cost Calculation Mystery

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 26 Jun 2013 21:20:28 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9A712D2_at_exmbx09.thus.corp>

At the moment the only thing I can think of is that the table stats and the index stats are not in synch. The selectivity of indx2 (two columns) has to be derived from the column num_distinct, but the selectivity of indx7 (single column) can be derived from the distinct_keys in the index. Depending on the sample sizes and timing the apparent number of distinct values (or the selectivity if there's a histogram in plan) could vary dramatically between user_tab_columns and user_indexes

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Martin Klier [usn_at_usn-it.de] Sent: 26 June 2013 21:23
To: oracle-l_at_freelists.org
Subject: Index Cost Calculation Mystery

  Access Path: index (RangeScan)
    Index: IDX2_AUFTRAG
    resc_io: 186.00 resc_cpu: 1585830
    ix_sel: 0.000034 ix_sel_with_filters: 0.000034     Cost: 94.49 Resp: 94.49 Degree: 1
  Access Path: index (AllEqRange)
    Index: IDX7_AUFTRAG
    resc_io: 1027.00 resc_cpu: 8999338
    ix_sel: 0.000241 ix_sel_with_filters: 0.000241     Cost: 521.97 Resp: 521.97 Degree: 1 ...
...
  Best:: AccessPath: IndexRange
  Index: IDX2_AUFTRAG

         Cost: 94.49 Degree: 1 Resp: 94.49 Card: 0.00 Bytes: 0 ======10053==========

But why is the cost of a AllEqRange on a single column index
(IDX7_AUFTRAG) 521.97 and an index range scan on a multi-column index
(IDX2_AUFTRAG) has only cost 94.49?--

http://www.freelists.org/webpage/oracle-l Received on Wed Jun 26 2013 - 23:20:28 CEST