關於全局索引和本地索引的優缺點，分別應該在什麼情況下使用，這方面的資料很多，本文不作討論。本文討論一種特殊情況，即建立在分區鍵之上的本地索引。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系統中，分區表的索引應該傾向於考慮使用全局索引。不幸的是，在一個省集中的關鍵系統中就由於將分區列建成了全局索引，導致了嚴重的性能問題。
