oracle数据库表分区

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 对分区表索引的影响

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分区没有数据的时候，本地索引和主键是好的可用状态

总结：