技术开发 频道

Oracle分区键与分区本地索引

【IT168 技术文档】关于全局索引和本地索引的优缺点,分别应该在什么情况下使用,这方面的资料很多,本文不作讨论。本文讨论一种特殊情况,即建立在分区键之上的本地索引。9i也算是很老的Oracle版本了,只是很多系统包括很多大型的核心的系统都在用,因此本文介绍建立在分区键列上的本地索引存在的问题。下面是一些测试: 

1   1   SQL> create table t1 ( a int, b varchar2(300)) partition by range(a)  
2   2  (  
3   3   partition p01 values less than (1000),  
4   4   partition p02 values less than (2000),  
5   5   partition p03 values less than (3000),  
6   6   partition p04 values less than (4000),  
7   7   partition p05 values less than (5000),  
8   8   partition p06 values less than (6000),  
9   9   partition p07 values less than (7000),  
10 10   partition p08 values less than (8000),  
11 11   partition p09 values less than (9000),  
12 12   partition p10 values less than (10000),  
13 13   partition p11 values less than (11000),  
14 14   partition p12 values less than (12000),  
15 15   partition p13 values less than (13000),  
16 16   partition p14 values less than (14000),  
17 17   partition p15 values less than (15000),  
18 18   partition p16 values less than (16000),  
19 19   partition p17 values less than (17000),  
20 20   partition p18 values less than (18000),  
21 21   partition p19 values less than (19000),  
22 22   partition p20 values less than (20000)  
23 23  )  
24 24  /  
25   
26 表已创建。  
27   
28 SQL> insert into t1 select rownum,lpad('x',200,'x') from dual connect by rownum<20000;  
29   
30 已创建19999行。  
31   
32 SQL> commit;  
33   
34 提交完成。  
35   
36 SQL> insert /*+ append */ into t1 select * from t1;  
37   
38 已创建19999行。  
39   
40 SQL> commit;  
41   
42 提交完成。  
43   
44 SQL> insert /*+ append */ into t1 select * from t1;  
45   
46 已创建39998行。  
47   
48 SQL> commit;  
49   
50 提交完成。  
51   
52 SQL> insert /*+ append */ into t1 select * from t1;  
53   
54 已创建79996行。  
55   
56 SQL> commit;  
57   
58 提交完成。  
59   
60 SQL> insert /*+ append */ into t1 select * from t1;  
61   
62 已创建159992行。  
63   
64 SQL> commit;  
65   
66 提交完成。  
67   
68 SQL> insert /*+ append */ into t1 select * from t1;  
69   
70 已创建319984行。  
71   
72 SQL> commit;  
73   
74 提交完成。

 

  首先建立一个测试范围分区表,分区键列是”a”,共20个分区,在这个测试表中生成约64万行数据。下面在列a上建本地索引并收集统计信息: 

1 SQL> create index t1_idx on t1(a) local;  
2   
3 索引已创建。  
4   
5 SQL> exec dbms_stats.gather_table_stats(user,'T1',  
6     method_opt=>'for all columns size 1',cascade=>true);  
7   
8 PL/SQL 过程已成功完成。  
9 SQL> @sosi  
10   
11 Please enter Name of Table Owner (Null = TEST):  
12 Please enter Table Name to show Statistics for: t1  
13   
14 ***********  
15 Table Level  
16 ***********  
17   
18 Table                   Number                 Empty  
19 Name                   of Rows   Blocks       Blocks  
20 --------------- -------------- -------- ------------  
21 T1                     639,968   18,880            0  
22   
23 Column                    Column                       Distinct            Number       Number  
24 Name                      Details                        Values   Density Buckets        Nulls  
25 ------------------------- ------------------------ ------------ --------- ------- ------------  
26 A                         NUMBER(22)                     19,999   .000050       1            0  
27 B                         VARCHAR2(300)                       1  1.000000       1            0  
28   
29                               B  
30 Index                      Tree     Leaf       Distinct         Number      Cluster  
31 Name            Unique    Level     Blks           Keys        of Rows       Factor  
32 --------------- --------- ----- -------- -------------- -------------- ------------  
33 T1_IDX          NONUNIQUE     1    1,390         19,999        639,968      639,968  
34   
35 Index           Column                     Col Column  
36 Name            Name                       Pos Details  
37 --------------- ------------------------- ---- ------------------------  
38 T1_IDX          A                            1 NUMBER(22)  
39   
40 ***************  
41 Partition Level  
42 ***************  
43   
44   Part Partition               Number                 Empty  
45    Pos Name                   of Rows   Blocks       Blocks  
46 ------ --------------- -------------- -------- ------------  
47      1 P01                     31,968      944            0  
48      2 P02                     32,000      944            0  
49      3 P03                     32,000      944            0  
50      4 P04                     32,000      944            0  
51      5 P05                     32,000      944            0  
52      6 P06                     32,000      944            0  
53      7 P07                     32,000      944            0  
54      8 P08                     32,000      944            0  
55      9 P09                     32,000      944            0  
56     10 P10                     32,000      944            0  
57     11 P11                     32,000      944            0  
58     12 P12                     32,000      944            0  
59     13 P13                     32,000      944            0  
60     14 P14                     32,000      944            0  
61     15 P15                     32,000      944            0  
62     16 P16                     32,000      944            0  
63     17 P17                     32,000      944            0  
64     18 P18                     32,000      944            0  
65     19 P19                     32,000      944            0  
66     20 P20                     32,000      944            0  
67                                     B  
68 Index           Partition        Tree     Leaf       Distinct         Number  
69 Name            Name            Level     Blks           Keys        of Rows  
70 --------------- --------------- ----- -------- -------------- --------------  
71 T1_IDX          P01                 1       67            999         31,968  
72 T1_IDX          P02                 1       67          1,000         32,000  
73 T1_IDX          P03                 1       67          1,000         32,000  
74 T1_IDX          P04                 1       67          1,000         32,000  
75 T1_IDX          P05                 1       67          1,000         32,000  
76 T1_IDX          P06                 1       67          1,000         32,000  
77 T1_IDX          P07                 1       67          1,000         32,000  
78 T1_IDX          P08                 1       67          1,000         32,000  
79 T1_IDX          P09                 1       67          1,000         32,000  
80 T1_IDX          P10                 1       67          1,000         32,000  
81 T1_IDX          P11                 1       72          1,000         32,000  
82 T1_IDX          P12                 1       72          1,000         32,000  
83 T1_IDX          P13                 1       72          1,000         32,000  
84 T1_IDX          P14                 1       72          1,000         32,000  
85 T1_IDX          P15                 1       72          1,000         32,000  
86 T1_IDX          P16                 1       72          1,000         32,000  
87 T1_IDX          P17                 1       72          1,000         32,000  
88 T1_IDX          P18                 1       72          1,000         32,000  
89 T1_IDX          P19                 1       72          1,000         32,000  
90 T1_IDX          P20                 1       72          1,000         32,000  

 


  下面执行查询:

1 SQL> set arraysize 1000  
2 SQL> set autot traceonly  
3 SQL> select * from t1 where a=1000;  
4   
5 已选择32行。  
6   
7 Execution Plan  
8 ----------------------------------------------------------  
9    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=33 Card=32 Bytes=652  
10           8)  
11   
12    1    0   TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T1' (Cost=33 Card=  
13           32 Bytes=6528)  
14   
15    2    1     INDEX (RANGE SCAN) OF 'T1_IDX' (NON-UNIQUE) (Cost=1 Card  
16           =32)  
17   
18 Statistics  
19 ----------------------------------------------------------  
20           0  recursive calls  
21           0  db block gets  
22          40  consistent gets  
23           0  physical reads  
24         300  redo size  
25         828  bytes sent via SQL*Net to client  
26         503  bytes received via SQL*Net from client  
27           2  SQL*Net roundtrips to/from client  
28           0  sorts (memory)  
29           0  sorts (disk)  
30          32  rows processed  


  这里看上去没有什么问题。

  下面再看看全局索引的情况: 

  1 SQL> drop index t1_idx;  
  2   
  3 索引已丢弃。  
  4   
  5 SQL> create index t1_idx on t1(a) ;  
  6   
  7 索引已创建。  
  8   
  9 SQL> exec dbms_stats.gather_table_stats(user,'T1',  
10     method_opt=>'for all columns size 1',cascade=>true);  
11   
12 PL/SQL 过程已成功完成。  
13   
14 SQL> @sosi  
15   
16 Please enter Name of Table Owner (Null = TEST):  
17 Please enter Table Name to show Statistics for: t1  
18   
19 ***********  
20 Table Level  
21 ***********  
22   
23 Table                   Number                 Empty  
24 Name                   of Rows   Blocks       Blocks  
25 --------------- -------------- -------- ------------  
26 T1                     639,968   18,880            0  
27   
28 Column                    Column                       Distinct            Number  
29 Name                      Details                        Values   Density Buckets  
30 ------------------------- ------------------------ ------------ --------- -------  
31 A                         NUMBER(22)                     19,999   .000050       1  
32 B                         VARCHAR2(300)                       1  1.000000       1  
33   
34                               B  
35 Index                      Tree     Leaf       Distinct         Number  
36 Name            Unique    Level     Blks           Keys        of Rows  
37 --------------- --------- ----- -------- -------------- --------------  
38 T1_IDX          NONUNIQUE     2    1,737         19,999        639,968  
39   
40 Index           Column                     Col Column  
41 Name            Name                       Pos Details  
42 --------------- ------------------------- ---- ------------------------  
43 T1_IDX          A                            1 NUMBER(22)  
44   
45 ***************  
46 Partition Level  
47 ***************  
48   
49   Part Partition               Number                 Empty  
50    Pos Name                   of Rows   Blocks       Blocks  
51 ------ --------------- -------------- -------- ------------  
52      1 P01                     31,968      944            0  
53      2 P02                     32,000      944            0  
54      3 P03                     32,000      944            0  
55      4 P04                     32,000      944            0  
56      5 P05                     32,000      944            0  
57      6 P06                     32,000      944            0  
58      7 P07                     32,000      944            0  
59      8 P08                     32,000      944            0  
60      9 P09                     32,000      944            0  
61     10 P10                     32,000      944            0  
62     11 P11                     32,000      944            0  
63     12 P12                     32,000      944            0  
64     13 P13                     32,000      944            0  
65     14 P14                     32,000      944            0  
66     15 P15                     32,000      944            0  
67     16 P16                     32,000      944            0  
68     17 P17                     32,000      944            0  
69     18 P18                     32,000      944            0  
70     19 P19                     32,000      944            0  
71     20 P20                     32,000      944            0  
72 Partition       Column  Distinct            Number Number  
73 Name            Name      Values   Density Buckets  Nulls  
74 --------------- ------- -------- --------- ------- ------  
75 P01             A            999   .001001       1      0  
76                       B              1  1.000000       1      0  
77 P02             A          1,000   .001000       1      0  
78                       B              1  1.000000       1      0  
79 P03             A          1,000   .001000       1      0  
80                       B              1  1.000000       1      0  
81 P04             A          1,000   .001000       1      0  
82                       B              1  1.000000       1      0  
83 P05             A          1,000   .001000       1      0  
84                       B              1  1.000000       1      0  
85 P06             A          1,000   .001000       1      0  
86                       B              1  1.000000       1      0  
87 P07             A          1,000   .001000       1      0  
88                       B              1  1.000000       1      0  
89 P08             A          1,000   .001000       1      0  
90                       B              1  1.000000       1      0  
91 P09             A          1,000   .001000       1      0  
92                       B              1  1.000000       1      0  
93 P10             A          1,000   .001000       1      0  
94                       B              1  1.000000       1      0  
95 P11             A          1,000   .001000       1      0  
96                       B              1  1.000000       1      0  
97 P12             A          1,000   .001000       1      0  
98                       B              1  1.000000       1      0  
99 P13             A          1,000   .001000       1      0  
100                     B              1  1.000000       1      0  
101 P14           A          1,000   .001000       1      0  
102                     B              1  1.000000       1      0  
103 P15             A          1,000   .001000       1      0  
104                      B              1  1.000000       1      0  
105 P16             A          1,000   .001000       1      0  
106                      B              1  1.000000       1      0  
107 P17             A          1,000   .001000       1      0  
108                      B              1  1.000000       1      0  
109 P18             A          1,000   .001000       1      0  
110                      B              1  1.000000       1      0  
111 P19             A          1,000   .001000       1      0  
112                      B              1  1.000000       1      0  
113 P20             A          1,000   .001000       1      0  
114                      B              1  1.000000       1      0  


        再次执行同样的查询: 

1 SQL> set arraysize 1000  
2 SQL> set autot traceonly  
3 SQL> select * from t1 where a=1000;  
4   
5 已选择32行。  
6   
7 Execution Plan  
8 ----------------------------------------------------------  
9    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=59 Card=32 Bytes=652  
10           8)  
11   
12    1    0   TABLE ACCESS (FULL) OF 'T1' (Cost=59 Card=32 Bytes=6528)  
13   
14 Statistics  
15 ----------------------------------------------------------  
16           0  recursive calls  
17           0  db block gets  
18         948  consistent gets  
19           0  physical reads  
20           0  redo size  
21         828  bytes sent via SQL*Net to client  
22         503  bytes received via SQL*Net from client  
23           2  SQL*Net roundtrips to/from client  
24           0  sorts (memory)  
25           0  sorts (disk)  
26          32  rows processed


  可以看到,这一次Oracle选择了分区剪裁之后的全表扫描,也就是扫描了整个分区,而没有使用索引。

  再看看使用HINT强制使用索引:

1 SQL> select /*+ index(t1) */ * from t1 where a=1000;  
2   
3 已选择32行。  
4   
5 Execution Plan  
6 ----------------------------------------------------------  
7    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=644 Card=32 Bytes=65  
8           28)  
9   
10    1    0   TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'T1' (Cost=644 Car  
11           d=32 Bytes=6528)  
12   
13    2    1     INDEX (RANGE SCAN) OF 'T1_IDX' (NON-UNIQUE) (Cost=4 Card  
14           =640)  
15   
16 Statistics  
17 ----------------------------------------------------------  
18           0  recursive calls  
19           0  db block gets  
20          37  consistent gets  
21           0  physical reads  
22           0  redo size  
23         828  bytes sent via SQL*Net to client  
24         503  bytes received via SQL*Net from client  
25           2  SQL*Net roundtrips to/from client  
26           0  sorts (memory)  
27           0  sorts (disk)  
28          32  rows processed

  可以看到,这里使用HINT,让SQL强制使用索引之后,逻辑读只有37(为什么比本地索引时还低,此处不深究)。显然,Oracle此前选择全表(分区)扫描的执行计划是错误的。

  为什么会出现这样的情况?这得从通过索引访问表数据的成本说起:

1 cost =  
2         blevel +  
3         ceiling(leaf_blocks * effective index selectivity) +  
4         ceiling(clustering_factor * effective table selectivity)

 

 
  简单地说成本分为三部分:

  访问索引分枝节点的成本。
  访问索引叶节点的成本。
  通过索引取得ROWID,通过ROWID回表访问表数据的成本。
  在上面的测试中,使用全局索引时,强制使用索引时,CBO评估的成本中,扫描索引的成本仅仅为4,而回表的成本高达640。能扫描分区P2评估的成本仅为59,自然CBO就选用了全表扫描,而不是索引扫描。

  那么接下来的问题是,为什么回表的成本那么高?看看回表部分成本,表的有效选择率,这里由于索引列也是分区列,Oracle应用了分区剪裁,然后取了剪裁后的分区P2的A列的选择率来作为公式里的选择率,即0.001,再看看索引的clustering factor,为639968(与表的行数一致,表明是最差情况下的聚集因子了),二者相乘再四舍五入就是640。这就是CBO估算的表访问部分的成本。这里的问题是,全局索引的clustering factor是针对整个表中所有数据的,这里取的选择率只是针对P2这个分区的。clustering factor可以简单理解为通过索引访问完表中的所有数据需要的IO数量,这里就相当于访问每一行都需要1次IO,然而实际上P2分区仅仅不过32000行数据,最差情况下,0.001选择的数据,IO数量也才32。

  在使用本地索引时,同样由于分区剪裁,取的是P2分区A列上的选择率,但是索引也只取了P2分区,clustering factor大大减少(为32000)。这样计算的成本也只有全局索引的1/20。

  使用HINT后的结果,证明索引也是很有效率的,跟本地索引几乎一样。只是由于Oracle成本计算的问题,导致了本文测试过徎中出现的情况。

  在10g中,回表成本计算的“表有效选择率”使用了索引的distinct keys,所以这就避免了这个问题。(注:这是指组成索引的所有列上都有相等条件比较的简单情况)

  分区键上的列,在建立索引时,很显然应该考虑建为本地索引,本文从Oracle的一个BUG也表明的确应该这样做,尽管在OLTP系统中,分区表的索引应该倾向于考虑使用全局索引。不幸的是,在一个省集中的关键系统中就由于将分区列建成了全局索引,导致了严重的性能问题。

  --The End

        查看原文: http://www.laoxiong.net/partition-key-and-local-index.html

0
相关文章