分区键与分区本地索引
Posted by 老熊 on 7月 13th, 2010关于全局索引和本地索引的优缺点，分别应该在什么情况下使用，这方面的资料很多，本文不作讨论。本文讨论一种特殊情况，即建立在分区键之上的本地索引。9i也算是很老的Oracle版本了，只是很多系统包括很多大型的核心的系统都在用，因此本文介绍建立在分区键列上的本地索引存在的问题。下面是一些测试：
SQL> create table t1 ( a int, b varchar2(300)) partition by range(a)
2 (
3 partition p01 values less than (1000),
4 partition p02 values less than (2000),
5 partition p03 values less than (3000),
6 partition p04 values less than (4000),
7 partition p05 values less than (5000),
8 partition p06 values less than (6000),
9 partition p07 values less than (7000),
10 partition p08 values less than (8000),
11 partition p09 values less than (9000),
12 partition p10 values less than (10000),
13 partition p11 values less than (11000),
14 partition p12 values less than (12000),
15 partition p13 values less than (13000),
16 partition p14 values less than (14000),
17 partition p15 values less than (15000),
18 partition p16 values less than (16000),
19 partition p17 values less than (17000),
20 partition p18 values less than (18000),
21 partition p19 values less than (19000),
22 partition p20 values less than (20000)
23 )
24 /
表已创建。
SQL> insert into t1 select rownum,lpad('x',200,'x') from dual connect by rownum<20000;
已创建19999行。
SQL> commit;
提交完成。
SQL> insert /*+ append */ into t1 select * from t1;
已创建19999行。
SQL> commit;
提交完成。
SQL> insert /*+ append */ into t1 select * from t1;
已创建39998行。
SQL> commit;
提交完成。
SQL> insert /*+ append */ into t1 select * from t1;
已创建79996行。
SQL> commit;
提交完成。
SQL> insert /*+ append */ into t1 select * from t1;
已创建159992行。
SQL> commit;
提交完成。
SQL> insert /*+ append */ into t1 select * from t1;
已创建319984行。
SQL> commit;
提交完成。
首先建立一个测试范围分区表，分区键列是”a”，共20个分区，在这个测试表中生成约64万行数据。下面在列a上建本地索引并收集统计信息：
SQL> create index t1_idx on t1(a) local; 索引已创建。 SQL> exec dbms_stats.gather_table_stats(user,'T1', method_opt=>'for all columns size 1',cascade=>true); PL/SQL 过程已成功完成。 SQL> @sosi Please enter Name of Table Owner (Null = TEST): Please enter Table Name to show Statistics for: t1 *********** Table Level *********** Table Number Empty Name of Rows Blocks Blocks --------------- -------------- -------- ------------ T1 639,968 18,880 0 Column Column Distinct Number Number Name Details Values Density Buckets Nulls ------------------------- ------------------------ ------------ --------- ------- ------------ A NUMBER(22) 19,999 .000050 1 0 B VARCHAR2(300) 1 1.000000 1 0 B Index Tree Leaf Distinct Number Cluster Name Unique Level Blks Keys of Rows Factor --------------- --------- ----- -------- -------------- -------------- ------------ T1_IDX NONUNIQUE 1 1,390 19,999 639,968 639,968 Index Column Col Column Name Name Pos Details --------------- ------------------------- ---- ------------------------ T1_IDX A 1 NUMBER(22) *************** Partition Level *************** Part Partition Number Empty Pos Name of Rows Blocks Blocks ------ --------------- -------------- -------- ------------ 1 P01 31,968 944 0 2 P02 32,000 944 0 3 P03 32,000 944 0 4 P04 32,000 944 0 5 P05 32,000 944 0 6 P06 32,000 944 0 7 P07 32,000 944 0 8 P08 32,000 944 0 9 P09 32,000 944 0 10 P10 32,000 944 0 11 P11 32,000 944 0 12 P12 32,000 944 0 13 P13 32,000 944 0 14 P14 32,000 944 0 15 P15 32,000 944 0 16 P16 32,000 944 0 17 P17 32,000 944 0 18 P18 32,000 944 0 19 P19 32,000 944 0 20 P20 32,000 944 0 B Index Partition Tree Leaf Distinct Number Name Name Level Blks Keys of Rows --------------- --------------- ----- -------- -------------- -------------- T1_IDX P01 1 67 999 31,968 T1_IDX P02 1 67 1,000 32,000 T1_IDX P03 1 67 1,000 32,000 T1_IDX P04 1 67 1,000 32,000 T1_IDX P05 1 67 1,000 32,000 T1_IDX P06 1 67 1,000 32,000 T1_IDX P07 1 67 1,000 32,000 T1_IDX P08 1 67 1,000 32,000 T1_IDX P09 1 67 1,000 32,000 T1_IDX P10 1 67 1,000 32,000 T1_IDX P11 1 72 1,000 32,000 T1_IDX P12 1 72 1,000 32,000 T1_IDX P13 1 72 1,000 32,000 T1_IDX P14 1 72 1,000 32,000 T1_IDX P15 1 72 1,000 32,000 T1_IDX P16 1 72 1,000 32,000 T1_IDX P17 1 72 1,000 32,000 T1_IDX P18 1 72 1,000 32,000 T1_IDX P19 1 72 1,000 32,000 T1_IDX P20 1 72 1,000 32,000
下面执行查询：
Read the rest of this entry »
Recent Comments