Oracle分区键与分区本地索引

牧3718

　　关于全局索引和本地索引的优缺点，分别应该在什么情况下使用，这方面的资料很多，本文不作讨论。本文讨论一种特殊情况，即建立在分区键之上的本地索引。9i也算是很老的Oracle版本了，只是很多系统包括很多大型的核心的系统都在用，因此本文介绍建立在分区键列上的本地索引存在的问题。下面是一些测试： 
<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->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上建本地索引并收集统计信息： 
<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->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  

　　
　　
　　下面执行查询：
<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->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)  
<span st