Oracle分区键与分区本地索引
luyued 发布于 2011-03-23 19:02 浏览 N 次
关于全局索引和本地索引的优缺点，分别应该在什么情况下使用，这方面的资料很多，本文不作讨论。本文讨论一种特殊情况，即建立在分区键之上的本地索引。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系统中，分区表的索引应该倾向于考虑使用全局索引。不幸的是，在一个省集中的关键系统中就由于将分区列建成了全局索引，导致了严重的性能问题。
- 07-01· 禁教唐诗算术能还幼儿快
- 07-01· 2011年06月17日
- 07-01· 唐诗宋词英译：李商隐 筹
- 07-01· 仿评《唐诗1000首》第186首
- 07-01· 没事干的时候背背唐诗吧
- 07-01· [转载]唐诗中“斜”字该读
- 07-01· 湖南醴陵瓷业转型升级
- 07-01· 奇瑞风云2两厢黑色|2010款
- 07-01· 摩根士丹利华鑫摩根士丹
- 07-01· 摩根士丹利华鑫近期优选
- 07-01· 中金投行部大摩出售中金
- 07-01· 摩根士丹利招聘6月2日【实
- 07-01· 营养防病圣典
- 07-01· 《博伽梵歌原意》之第十
- 07-01· [不错]斑斓圣典---减肥中常
- 07-01· 武乐圣典《太极武当》:武
- 07-01· 铁血英雄-现阶段战功牌兑
- 07-01· 2011年06月10日【原创】南歌
- 07-01· 【淘宝网信息】- 2010年的
- 07-01· 深圳品牌女装有哪些?
- 今日
- 本周
- 年度