1、构建实验表
sql> select *From wl;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
8102 FORD 17-4月 -83
7654 MARTIN 26-1月 -84
sql> create table t2 partition by range(hiredate)
2 (partition p1 values less than(to_date('1981-01-01','yyyy-mm-dd')) tablespace test,
3 partition p2 values less than(to_date('1982-01-01',
4 partition other values less than(maxvalue) tablespace test)
5 as select * from wl;
表已创建。
sql> select table_name,partition_name,partition_position from user_tab_partitions where table_name='T2';
TABLE_NAME PARTITION_NAME PARTITION_POSITION
------------------------------ ------------------------------ ------------------
T2 P1 1
T2 P2 2
T2 P3 3
创建索引:
sql> alter table t2 add constraints pk_t2 primary key(empno);
表已更改。
sql> create index local_ename on t2(ename) local;
索引已创建。
查看索引的状态:
SQL> select INDEX_NAME,status,PARTITION_NAME from DBA_ind_partitions WHERE PARTITION_NAME LIKE 'P%' and index_owner='SCOTT' ;
INDEX_NAME STATUS PARTITION_NAME
------------------------------ -------- ------------------------------
LOCAL_ENAME USABLE P1
LOCAL_ENAME USABLE P2
LOCAL_ENAME USABLE P3
sql> select INDEX_NAME,TABLE_NAME,STATUS from dba_indexes where table_name='T2';
INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_T2 T2 VALID
LOCAL_ENAME T2 N/A
从上面查询结果可以看出 本地索引 usable 可用,主键 valid 也是正常状态
sql> select *From t2 partition(p1);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
sql> select *From t2 partition(p2);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
sql> select *From t2 partition(p3);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-1月 -82 1300 10
8102 FORD 17-4月 -83
7654 MARTIN 26-1月 -84
split partition 对分区表索引的影响 @H_502_89@
2、 拆分分区对分区表索引的影响
SQL> alter table t2 split partition p3 at(to_date('1983-01-01','yyyy-mm-dd')) into (partition p3,partition other);
表已更改。
sql> select *From t2 partition(p3);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-1月 -82 1300 10
sql> select *From t2 partition(other);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
8102 FORD 17-4月 -83
7654 MARTIN 26-1月 -84
我们可以看到p3 和other 分区都有数据,再来查看索引的状态
sql> select INDEX_NAME,PARTITION_NAME from DBA_ind_partitions WHERE PARTITION_NAME LIKE 'P%' and index_owner='SCOTT' ;
INDEX_NAME STATUS PARTITION_NAME
------------------------------ -------- ------------------------------
LOCAL_ENAME USABLE P1
LOCAL_ENAME USABLE P2
LOCAL_ENAME UNUSABLEP3
sql> select INDEX_NAME,STATUS from dba_indexes where table_name='T2';
INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_T2 T2 UNUSABLE
LOCAL_ENAME T2 N/A
sql> alter table t2 split partition p3 at(to_date('1983-01-01',partition other);
表已更改。
从上面我们可以看出拆分成p3,other分区都有数据的情况下,分区的索引变成unusable,主键也是unusable
我们把索引rebuild好后,继续对other 分区拆分,拆分成 无数据的p4和有数据的other分区,看看索引的状态
sql> alter index pk_t2 rebuild online;
索引已更改。
sql> alter index local_ename rebuild partition p3 online ;
索引已更改。
sql> select INDEX_NAME,STATUS from dba_indexes where table_name='T2';
INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_T2 T2 VALID
LOCAL_ENAME T2 N/A
sql> select INDEX_NAME,PARTITION_NAME from DBA_ind_partitions WHERE PARTITION_NAME LIKE 'P%' and index_owner='SCOTT' ;
INDEX_NAME STATUS PARTITION_NAME
------------------------------ -------- ------------------------------
LOCAL_ENAME USABLE P1
LOCAL_ENAME USABLE P2
LOCAL_ENAME USABLE P3
在此索引已经恢复好了,进行拆分拆分成 无数据的p4和有数据的other分区,看看索引的状态
sql> alter table t2 split partition other at(to_date('1983-04-01','yyyy-mm-dd')) into (partition p4,partition other);
表已更改。
sql> select *from t2 partition(p4);
未选定行
sql> select *from t2 partition(other);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
8102 FORD 17-4月 -83
7654 MARTIN 26-1月 -84
sql> select INDEX_NAME,PARTITION_NAME from DBA_ind_partitions WHERE PARTITION_NAME LIKE 'P%' and index_owner='SCOTT' ;
INDEX_NAME STATUS PARTITION_NAME
------------------------------ -------- ------------------------------
LOCAL_ENAME USABLE P1
LOCAL_ENAME USABLE P2
LOCAL_ENAME USABLE P3
LOCAL_ENAME USABLE P4
sql> select INDEX_NAME,STATUS from dba_indexes where table_name='T2';
INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_T2 T2 VALID
LOCAL_ENAME T2 N/A
继续拆分,拆分成有数据的p5和无数据的other分区的时候,索引的状态
sql> alter table t2 split partition other at(to_date('1984-04-01','yyyy-mm-dd')) into (partition p5,partition other);
表已更改。
sql> select *from t2 partition(other);
未选定行
sql> select *from t2 partition(p5);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
8102 FORD 17-4月 -83
7654 MARTIN 26-1月 -84
sql> select INDEX_NAME,STATUS from dba_indexes where table_name='T2';
INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_T2 T2 VALID
LOCAL_ENAME T2 N/A
sql> select INDEX_NAME,PARTITION_NAME from DBA_ind_partitions WHERE PARTITION_NAME LIKE 'P%' and index_owner='SCOTT' ;
INDEX_NAME STATUS PARTITION_NAME
------------------------------ -------- ------------------------------
LOCAL_ENAME USABLE P1
LOCAL_ENAME USABLE P2
LOCAL_ENAME USABLE P3
LOCAL_ENAME USABLE P4
LOCAL_ENAME USABLE P5
我们可以发现当p5分区有数据而other分区没有数据的时候,本地索引和主键是好的可用状态
总结:
a,b分区均无数据 | a分区有数据 b分区无数据 |
a分区无数据 b分区有数据 |
a,b分区均有数 据 | ||
global index | VALID | VALID | VALID | UNUSABLE | |
local index | USABLE | USABLE | USABLE | UNUSABLE |
add partition 对索引的影响
sql> create table t2 partition by range(hiredate)
2 (partition p1 values less than(to_date('1981-01-01',
3 partition p2 values less than(to_date('1985-01-01','yyyy-mm-dd')) tablespace test
4 )
5 as select * from wl;
表已创建。
sql> alter table t2 add constraints pk_t2 primary key(empno);
表已更改。
sql> create index local_ename on t2(ename) local;
索引已创建。
sql> select INDEX_NAME,PARTITION_NAME from DBA_ind_partitions WHERE PARTITION_NAME LIKE 'P%' and index_owner='SCOTT' ;
INDEX_NAME STATUS PARTITION_NAME
------------------------------ -------- ------------------------------
LOCAL_ENAME USABLE P1
LOCAL_ENAME USABLE P2
sql> select INDEX_NAME,STATUS from dba_indexes where table_name='T2';
INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_T2 T2 VALID
LOCAL_ENAME T2 N/A
sql> alter table t2 add partition p3 values less than(to_date('1986-01-01','yyyy-mm-dd')) tablespace test ;
表已更改。
sql> select INDEX_NAME,PARTITION_NAME from DBA_ind_partitions WHERE PARTITION_NAME LIKE 'P%' and index_owner='SCOTT' ;
INDEX_NAME STATUS PARTITION_NAME
------------------------------ -------- ------------------------------
LOCAL_ENAME USABLE P1
LOCAL_ENAME USABLE P2
LOCAL_ENAME USABLE P3
sql> select INDEX_NAME,STATUS from dba_indexes where table_name='T2';
INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_T2 T2 VALID
LOCAL_ENAME T2 N/A
从此处可以看出添加分区对分区表索引没有任何影响
drop partition对索引的影响
刚才新添加的分区,目前p3分区里面没有任何数据,我们删除p3分区
sql> select *from t2 partition(p3);
未选定行
删除分区
sql> alter table t2 drop partition p3;
表已更改。
sql> select INDEX_NAME,STATUS from dba_indexes where table_name='T2';
INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_T2 T2 VALID
LOCAL_ENAME T2 N/A
sql> select INDEX_NAME,PARTITION_NAME from DBA_ind_partitions WHERE PARTITION_NAME LIKE 'P%' and index_owner='SCOTT' ;
INDEX_NAME STATUS PARTITION_NAME
------------------------------ -------- ------------------------------
LOCAL_ENAME USABLE P1
LOCAL_ENAME USABLE P2
删除空的分区对分区表索引没有任何影响
sql> alter table t2 add partition p3 values less than(to_date('1986-01-01','yyyy-mm-dd')) tablespace test ;
表已更改。
sql> insert into t2 (empno,ename,hiredate) values(7777,'aaaa',to_date('1985-07-01','yyyy-mm-dd'));
已创建 1 行。
sql> commit;
提交完成。
sql> select *from t2 partition(p3);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7777 aaaa 01-7月 -85
sql> alter table t2 drop partition p3;
表已更改。
sql> select INDEX_NAME,STATUS from dba_indexes where table_name='T2';
INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_T2 T2 UNUSABLE
LOCAL_ENAME T2 N/A
sql> select INDEX_NAME,PARTITION_NAME from DBA_ind_partitions WHERE PARTITION_NAME LIKE 'P%' and index_owner='SCOTT' ;
INDEX_NAME STATUS PARTITION_NAME
------------------------------ -------- ------------------------------
LOCAL_ENAME USABLE P1
LOCAL_ENAME USABLE P2
可以发现当删除有数据的分区时候,全局索引不可以用
truncate partition对索引的影响
2 (partition p1 values less than(to_date('1981-1-1',
3 partition p2 values less than(to_date('1982-1-1',
4 partition p3 values less than(maxvalue) tablespace test)
5 as select * from wl;
表已创建。
表已更改。
sql> create index local_ename on t2(ename) local;
索引已创建。
sql> select count(*)from t2 partition(p2);
COUNT(*)
----------
0
sql> select count(*)from t2 partition(p3);
COUNT(*)
----------
3
sql> select count(*)from t2 partition(p1);
COUNT(*)
----------
1
INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_T2 T2 VALID
LOCAL_ENAME T2 N/A
sql> select INDEX_NAME,PARTITION_NAME from DBA_ind_partitions WHERE PARTITION_NAME LIKE 'P%' and index_owner='SCOTT' ;
INDEX_NAME STATUS PARTITION_NAME
------------------------------ -------- ------------------------------
LOCAL_ENAME USABLE P1
LOCAL_ENAME USABLE P2
LOCAL_ENAME USABLE P3
我们可以看到p1分区里面无数据,其他分区里面都有数据,我们对p1分区truncate 一下
sql> alter table t2 truncate partition(p1);
表被截断。
sql> select INDEX_NAME,PARTITION_NAME from DBA_ind_partitions WHERE PARTITION_NAME LIKE 'P%' and index_owner='SCOTT' ;
INDEX_NAME STATUS PARTITION_NAME
------------------------------ -------- ------------------------------
LOCAL_ENAME USABLE P1
LOCAL_ENAME USABLE P2
LOCAL_ENAME USABLE P3
可以看出truncate一个空的分区,对于分区表的索引没有影响
sql> alter table t2 truncate partition(p2);
表被截断。
sql> select INDEX_NAME,STATUS from dba_indexes where table_name='T2';
INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_T2 T2 UNUSABLE
LOCAL_ENAME T2 N/A
sql> select INDEX_NAME,PARTITION_NAME from DBA_ind_partitions WHERE PARTITION_NAME LIKE 'P%' and index_owner='SCOTT' ;
INDEX_NAME STATUS PARTITION_NAME
------------------------------ -------- ------------------------------
LOCAL_ENAME USABLE P1
LOCAL_ENAME USABLE P2
LOCAL_ENAME USABLE P3
可以看出truncate一个非空的分区,对于分区表的全局索引有影响
@H_502_274@
add partition
drop partition
drop partition
truncate partition
truncate partition
分区表中无数据
分区表中有数据
分区表中无数据
分区表中有数据
global index
VALID
VALID
UNUSABLE
VALID
UNUSABLE
local index
USABLE
USABLE
USABLE
USABLE
USABLE