Oracle分区键与分区本地索引关于全局索引和本地索引的优缺点，分别应该在什么情况下使用，这方面的资料很多，
Oracle分区键与分区本地索引
关于全局索引和本地索引的优缺点，分别应该在什么情况下使用，这方面的资料很多，本文不作讨论。本文讨论一种特殊情况，即建立在分区键之上的本地索引。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
