Re: Index Cost Calculation Mystery

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Thu, 27 Jun 2013 03:10:18 +0400
Message-ID: <CAOVevU7=npZYP9TWXWMXe256M0jkZzm-5nN8G8GOZbqPWdU-DQ_at_mail.gmail.com>

Just simple example:
create table xt_test(pad,a,b) as

          select lpad('x',300), mod(level,10) a, 0 b from dual connect by level<=10000
union all select lpad('x',300), mod(level,10) a, 1 b from dual connect by level<=5000;
create index ix_xt_test_a on xt_test(a); create index ix_xt_test_ab on xt_test(a,b); exec dbms_stats.gather_table_stats('','XT_TEST'); exec dbms_stats.delete_index_stats('','IX_XT_TEST_AB');

Test queries:
1. select/*+ index(x ix_xt_test_a) */ sum(length(pad)) s from xt_test x where a=3;
2. select/*+ index(x ix_xt_test_ab) */ sum(length(pad)) s from xt_test x where a=3;

1. For index on A:

    Access Path: index (AllEqRange)

      Index: IX_XT_TEST_A
      resc_io: 656.00  resc_cpu: 5227515
      ix_sel: 0.100000  ix_sel_with_filters: 0.100000
      Cost: 656.26  Resp: 656.26  Degree: 1

    Best:: AccessPath: IndexRange
    Index: IX_XT_TEST_A

           Cost: 656.26 Degree: 1 Resp: 656.26 Card: 1500.00 Bytes: 0

2. For index on (A,B):

    Access Path: index (RangeScan)

      Index: IX_XT_TEST_AB
      resc_io: 84.00  resc_cpu: 690701
      ix_sel: 0.100000  ix_sel_with_filters: 0.100000
      Cost: 84.03  Resp: 84.03  Degree: 1

    Best:: AccessPath: IndexRange
    Index: IX_XT_TEST_AB

           Cost: 84.03 Degree: 1 Resp: 84.03 Card: 1500.00 Bytes: 0

Best regards,
Sayan Malakshinov
http://orasql.org

On Thu, Jun 27, 2013 at 1:20 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk
> wrote:

>
>
> 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
>
>
>

-- 
Best regards,
Sayan Malakshinov
Senior performance tuning engineer
PSBank
http://orasql.org


--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jun 27 2013 - 01:10:18 CEST