此小文不讨论分区索引的其它方面如:本地非前缀索引和本地前缀索引,以及分区裁剪和分区索引的主键问题,我们看看清理数据时需要注意的地方。
经常需要清理数据,而这些表中基本都是分区表。那么,我们如何来确定那些表分区可以清理(本地索引,partition可以truncate),如果有global索引,如何确定:
创建测试表:
create table test_par
(vid number ,
v_date number,
v_data varchar2(100))
partition by range(v_date)
(partition day_1 values less than (2 ),
partition day_2 values less than (3 ),
partition day_3 values less than (4 ),
partition day_4 values less than (5 ),
partition day_5 values less than (6 ),
partition day_6 values less than (7 ),
partition day_7 values less than (8 ),
partition day_8 values less than (9 ),
partition day_9 values less than (10),
partition day_10 values less than (11),
partition day_11 values less than (12),
partition day_12 values less than (13),
partition day_13 values less than (14),
partition day_14 values less than (15),
partition day_15 values less than (16),
partition day_16 values less than (17),
partition day_17 values less than (18),
partition day_18 values less than (19),
partition day_19 values less than (20),
partition day_20 values less than (21),
partition day_21 values less than (22),
partition day_22 values less than (23),
partition day_23 values less than (24),
partition day_24 values less than (25),
partition day_25 values less than (26),
partition day_26 values less than (27),
partition day_27 values less than (28),
partition day_28 values less than (29),
partition day_29 values less than (30),
partition day_30 values less than (31),
partition day_31 values less than (32),
partition day_max values less than (maxvalue)
);
插入测试数据:
SQL> insert into test
2 (vid,
3 v_date,
4 v_data)
5 select
6 level ,
7 mod(level,31)+1,
8 lpad(level,60,'0')
9 from dual
10 connect by level<=1000000;
1000000 rows created.
SQL> commit;
Commit complete.
创建索引global nonpartition index:
create index in1 on test(vid) tablespace sec;
然后收集统计信息:
begin
dbms_stats.gather_table_stats(ownname =>'SYS',
tabname =>'TEST',
estimate_percent =>5,
method_opt =>'FOR ALL COLUMNS SIZE AUTO',
degree =>4,
cascade =>TRUE);
end;
/
SQL> alter table test truncate partition day_31;
Table truncated.
我曾经犯过错误,清理表分区之前,我只是去确认dba_part_indexes里面没有找到global类型的索引,那么这个就是
只关注了global 分区索引的存在与否而忽略了normal的global索引。太危险的事情,要是白天业务高峰,truncate了
分区,索引失效,后果不看设想。
下面看看global partition index:
SQL> alter table test truncate partition day_30;
SQL> alter table test truncate partition day_29 update global indexes;
Table truncated.
你还敢在生产上如果存在global partition indexes时候使用update global indexes?
最后来看下我们如何判断索引是local的,这个就比较容易了。
SQL> create index in1 on test(v_date,vid) local tablespace sec;
Index created.
再创建一个global非分区索引:
SQL> create index in2 on test(vid) tablespace sec;
Index created.
可以根据如下脚本来查看有无global index:
select owner,
index_name
from dba_indexes
where table_name='&Table_name' and status in('VALID','N/A')
minus
select owner,
index_name
from dba_part_indexes
where table_name='&Table_name' and locality='LOCAL';
如果有返回行,那么你就要去关注了,看看是global partition index还是global nonpartition index。
略记于此。
经常需要清理数据,而这些表中基本都是分区表。那么,我们如何来确定那些表分区可以清理(本地索引,partition可以truncate),如果有global索引,如何确定:
创建测试表:
create table test_par
(vid number ,
v_date number,
v_data varchar2(100))
partition by range(v_date)
(partition day_1 values less than (2 ),
partition day_2 values less than (3 ),
partition day_3 values less than (4 ),
partition day_4 values less than (5 ),
partition day_5 values less than (6 ),
partition day_6 values less than (7 ),
partition day_7 values less than (8 ),
partition day_8 values less than (9 ),
partition day_9 values less than (10),
partition day_10 values less than (11),
partition day_11 values less than (12),
partition day_12 values less than (13),
partition day_13 values less than (14),
partition day_14 values less than (15),
partition day_15 values less than (16),
partition day_16 values less than (17),
partition day_17 values less than (18),
partition day_18 values less than (19),
partition day_19 values less than (20),
partition day_20 values less than (21),
partition day_21 values less than (22),
partition day_22 values less than (23),
partition day_23 values less than (24),
partition day_24 values less than (25),
partition day_25 values less than (26),
partition day_26 values less than (27),
partition day_27 values less than (28),
partition day_28 values less than (29),
partition day_29 values less than (30),
partition day_30 values less than (31),
partition day_31 values less than (32),
partition day_max values less than (maxvalue)
);
插入测试数据:
SQL> insert into test
2 (vid,
3 v_date,
4 v_data)
5 select
6 level ,
7 mod(level,31)+1,
8 lpad(level,60,'0')
9 from dual
10 connect by level<=1000000;
1000000 rows created.
SQL> commit;
Commit complete.
创建索引global nonpartition index:
create index in1 on test(vid) tablespace sec;
然后收集统计信息:
begin
dbms_stats.gather_table_stats(ownname =>'SYS',
tabname =>'TEST',
estimate_percent =>5,
method_opt =>'FOR ALL COLUMNS SIZE AUTO',
degree =>4,
cascade =>TRUE);
end;
/
来看看索引的信息:
select index_name,index_type,status from dba_indexes where table_name='TEST';
INDEX_NAME INDEX_TYPE STATUS
---------- ---------- ------
IN1 NORMAL VALID
那么我们truncate一个分区:SQL> alter table test truncate partition day_31;
Table truncated.
select index_name,index_type,status from dba_indexes where table_name='TEST';
INDEX_NAME INDEX_TYPE STATUS
---------- ---------- --------
IN1 NORMAL UNUSABLE
那么我们drop掉次索引,再来创建带有global的索引:
create index in1 on test(vid) global tablespace sec;
INDEX_NAME INDEX_TYPE STATUS
---------- ---------- ------
IN1 NORMAL VALID
对于在创建索引时未指定global字段或者local字段,默认索引类型就是全局非分区索引。我曾经犯过错误,清理表分区之前,我只是去确认dba_part_indexes里面没有找到global类型的索引,那么这个就是
只关注了global 分区索引的存在与否而忽略了normal的global索引。太危险的事情,要是白天业务高峰,truncate了
分区,索引失效,后果不看设想。
下面看看global partition index:
SQL> create index in1 on test(vid) global
2 partition by range(vid)
3 (
4 partition part_1 values less than(300000),
5 partition part_2 values less than(600000),
6 partition part_max values less than(MAXVALUE)
7 )
8 /
Index created.
1.
select index_name,index_type,status from dba_indexes where table_name='TEST';
INDEX_NAME INDEX_TYPE STATUS
---------- ---------- --------
IN1 NORMAL N/A
看到没有,global partition index在dba_indexes里面状态N/A.
2.
select index_name,partition_name,
status
from dba_ind_partitions where index_name='IN1';
INDEX_NAME PARTITION_NAME STATUS
---------- ------------- ------
IN1 PART_1 USABLE
IN1 PART_2 USABLE
IN1 PART_MAX USABLE
3.
select index_name,locality from dba_part_indexes where table_name='TEST';
INDEX_NAME LOCALITY
---------- --------
IN1 GLOBAL
那么我要是在truncate掉一个分区呢?SQL> alter table test truncate partition day_30;
Table truncated.
SQL> alter table test truncate partition day_30;
Table truncated.
1.
select index_name,index_type,status from dba_indexes where table_name='TEST';
INDEX_NAME INDEX_TYPE STATUS
--------- ---------- ------
IN1 NORMAL N/A
这时候global partition index在dba_indexes里面的status是没有变化的。
2.
select index_name,partition_name,
status
from dba_ind_partitions where index_name='IN1';
INDEX_NAME PARTITION_NAME STATUS
--------- -------------- ------
IN1 PART_1 UNUSABLE
IN1 PART_2 UNUSABLE
IN1 PART_MAX UNUSABLE
3.
select index_name,locality from dba_part_indexes where table_name='TEST';
INDEX_NAME LOCALITY
--------- --------
IN1 GLOBAL
当然,我们如果使用update global index,dba_ind_partitions里面的状态是什么呢,看下面神奇的情况.SQL> alter table test truncate partition day_29 update global indexes;
Table truncated.
select index_name,partition_name,
status
from dba_ind_partitions where index_name='IN1';
INDEX_NAME PARTITION_NAME STATUS
---------- -------------- ------
IN1 PART_1 UNUSABLE
IN1 PART_2 UNUSABLE
IN1 PART_MAX UNUSABLE
索引的状态还是UNUSABLE,我们先抛弃使用update global indexes选项加锁对db的影响,以及会出现library cache等事件的影响。你还敢在生产上如果存在global partition indexes时候使用update global indexes?
最后来看下我们如何判断索引是local的,这个就比较容易了。
SQL> create index in1 on test(v_date,vid) local tablespace sec;
Index created.
1.
select index_name,index_type,status from dba_indexes where table_name='TEST';
INDEX_NAME INDEX_TYPE STATUS
--------- ---------- ------
IN1 NORMAL N/A
2.
select index_name,partition_name,
status
from dba_ind_partitions where index_name='IN1';
INDEX_NAME PARTITION_NAME STATUS
--------- ------------- ------
IN1 DAY_1 USABLE
IN1 DAY_10 USABLE
IN1 DAY_11 USABLE
IN1 DAY_12 USABLE
IN1 DAY_13 USABLE
IN1 DAY_14 USABLE
IN1 DAY_15 USABLE
IN1 DAY_16 USABLE
IN1 DAY_17 USABLE
IN1 DAY_18 USABLE
IN1 DAY_19 USABLE
IN1 DAY_2 USABLE
IN1 DAY_20 USABLE
IN1 DAY_21 USABLE
IN1 DAY_22 USABLE
IN1 DAY_23 USABLE
IN1 DAY_24 USABLE
IN1 DAY_25 USABLE
IN1 DAY_26 USABLE
IN1 DAY_27 USABLE
IN1 DAY_28 USABLE
IN1 DAY_29 USABLE
IN1 DAY_3 USABLE
IN1 DAY_30 USABLE
IN1 DAY_4 USABLE
IN1 DAY_5 USABLE
IN1 DAY_6 USABLE
IN1 DAY_7 USABLE
IN1 DAY_8 USABLE
IN1 DAY_9 USABLE
IN1 DAY_MAX USABLE
3.
select index_name,locality from dba_part_indexes where table_name='TEST';
INDEX_NAME LOCALITY
---------- --------
IN1 LOCAL
分区索引你就可以truncate一个分区,而不影响其它的了。再创建一个global非分区索引:
SQL> create index in2 on test(vid) tablespace sec;
Index created.
可以根据如下脚本来查看有无global index:
select owner,
index_name
from dba_indexes
where table_name='&Table_name' and status in('VALID','N/A')
minus
select owner,
index_name
from dba_part_indexes
where table_name='&Table_name' and locality='LOCAL';
如果有返回行,那么你就要去关注了,看看是global partition index还是global nonpartition index。
略记于此。