Shulou(Shulou.com)06/01 Report--

In the speech of Elder Yang at the annual meeting of ACOUG, a problem was mentioned.

A SQL statement, the cost value of the two execution plans is the same, how does CBO choose the execution plan?

Translate the question and create the test table data

SQL > create table z (a number, b number)

Table created.

SQL > begin

2 for i in 1.. 10000 loop

3 insert into z values (I, I)

4 end loop

5 commit

6 end

7 /

PL/SQL procedure successfully completed.

SQL > create index idx_z_01 on z (a)

Index created.

SQL > create index idx_z_02 on z (b)

Index created.

SQL > exec dbms_stats.gather_table_stats ('BISAL',' Zero, cascade= > true)

PL/SQL procedure successfully completed.

For the following SQL

Select * from z where axi1 and bliss 1

According to the 10053 display, you can see that the two indexes, IDX_Z_01 and IDX_Z_02, are the same for cost. What execution plan will CBO choose?

Note: for an introduction to 10053, please refer to "exploring the Mysteries of the Index-10053 event".

Index Stats::

Index: IDX_Z_01 Col#: 1

LVLS: 1 # LB: 20 # DK: 10000 LB/K: 1.00 DB/K: 1.00 CLUF: 18.00

Index: IDX_Z_02 Col#: 2

LVLS: 1 # LB: 20 # DK: 10000 LB/K: 1.00 DB/K: 1.00 CLUF: 18.00

Access Path: index (AllEqRange)

Index: IDX_Z_01

Resc_io: 2.00 resc_cpu: 14613

Ix_sel: 0.000100 ix_sel_with_filters: 0.000100

Cost: 2.00 Resp: 2.00 Degree: 1

Access Path: index (AllEqRange)

Index: IDX_Z_02

Resc_io: 2.00 resc_cpu: 14613

Ix_sel: 0.000100 ix_sel_with_filters: 0.000100

Cost: 2.00 Resp: 2.00 Degree: 1

Elder Yang mentioned that dbsnake once wrote and searched, and there was an article introducing

"CBO's selection of indexes with the same Cost value"

Http://www.dbsnake.net/handle-equally-costed-indexes.html

To sum up the article

For Oracle 10gR2 and above, CBO's choice of indexes with the same Cost value actually looks like this:

1. If the number of leaf blocks is different for an index with the same Cost value, Oracle selects the index with a smaller number of leaf blocks.

two。 If an index with the same Cost value has the same number of leaf blocks, Oracle selects the index with the alphabetical order of the index name.

First verify the point of view of (2), from the above 10053, we can see that the cost of the two indexes is the same, and the number of leaf blocks is the same. At this time, CBO chooses IDX_Z_01, because his name comes before IDX_Z_02.

Best:: AccessPath: IndexRange

Index: IDX_Z_01

Cost: 2.00 Degree: 1 Resp: 2.00 Card: 0.00 Bytes: 0

Execution plan display, using index IDX_Z_01

In addition to the index name and the index leaf block, I suspected that the clustering factor would be another factor, but by setting the clsfct of IDX_Z_01 is higher than the clsfct of IDX_Z_02.

Exec dbms_stats.set_index_stats ('BISAL','IDX_Z_01',clstfct= > 21)

According to 10053, IDX_Z_01 's clsfct is higher than IDX_Z_02 's clsfct.

Index Stats::

Index: IDX_Z_01 Col#: 1

LVLS: 1 # LB: 100 # DK: 10000 LB/K: 1.00 DB/K: 1.00 CLUF: 21.00

Index: IDX_Z_02 Col#: 2

LVLS: 1 # LB: 20 # DK: 10000 LB/K: 1.00 DB/K: 1.00 CLUF: 18.00

At this time, CBO chose IDX_Z_01, because his name, ranked before IDX_Z_02, did not take into account the influence of clustering factors.

Best:: AccessPath: IndexRange

Index: IDX_Z_01

Cost: 2.00 Degree: 1 Resp: 2.00 Card: 0.00 Bytes: 0

Summary:

For the same index of cost, 10gR2 and above, Oracle CBO still has a method to choose, index leaf block is the first condition, index name sorting is the second condition.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.