oracle数据库表分区

前端之家收集整理的这篇文章主要介绍了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 对分区表索引的影响 @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

我们可以发现当p4分区没有数据而other分区有数据的时候,本地索引和主键是好的可用状态



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

总结:

@H_502_274@
split partition ( split partition a into a and b )
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对索引的影响

sql> create table t2 partition by range(hiredate)
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> alter table t2 add constraints pk_t2 primary key(empno);
表已更改。
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
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

我们可以看到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

原文链接:https://www.f2er.com/oracle/208296.html

猜你在找的Oracle相关文章