oracle数据库表分区

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

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

总结：

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一个非空的分区，对于分区表的全局索引有影响

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相关文章

[Oracle]记一次由sequence引发的enq sv-contention等待事件
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...
oracle统计信息的锁定与解锁
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...
【转】RMAN删除过期备份或非过期备份
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...
数据恢复顾问（DRA）
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...
RMAN备份与恢复（一）--认识RMAN
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...
RMAN备份与恢复（二）--RMAN常用操作学习
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...
ORA-00245问题总结
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...
使用RMAN对数据文件进行恢复
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...
Oracle使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问