Re: Index Cost Calculation Mystery
- From: Sayan Malakshinov <xt.and.r@xxxxxxxxx>
- To: "usn@xxxxxxxxx" <usn@xxxxxxxxx>
- Date: Thu, 27 Jun 2013 03:10:18 +0400
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@xxxxxxxxxxxxxxxxxx
> 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@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
> behalf of Martin Klier [usn@xxxxxxxxx]
> Sent: 26 June 2013 21:23
> To: oracle-l@xxxxxxxxxxxxx
> 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?--
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Best regards,
Sayan Malakshinov
Senior performance tuning engineer
PSBank
http://orasql.org
--
//www.freelists.org/webpage/oracle-l
Other related posts:
