加载中…分区表、分区索引（三）——Modify,Move,Rename,Split,Truncate,Drop,Rebuild Index
(2014-07-31 16:12:03)
标签：
itoraclepartitionindex |
分类： technology |
Modifying
default attributes
用于修改一个表、表分区的默认属性，在下一次创建分区、子分区时使用此默认属性。
1. 修改表的默认属性，hash分区表只能修改tablespace属性，分区将继承此属性：
ALTER TABLE ptable MODIFY DEFAULT ATTRIBUTES ....;
2. 修改分区的默认属性，子分区将继承此属性
ALTER TABLE ptable MODIFY DEFAULT ATTRIBUTES FOR PARTITION pname ...;
3. 分区索引与分区表类似的命令
例如: 修改表emp_list的属性：pctfree=15, nologging, compress
KN @ ORCL>SELECT table_name,
2 partition_name,
3 high_value,
4 partition_position,
5 tablespace_name,
6 pct_free,
7 compression,
8 logging
9 FROM user_tab_partitions
10 WHERE table_name = 'EMP_LIST';
TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION TABLESPACE_NAME PCT_FREE COMPRESS LOGGING
--------------- --------------- ------------------------- ------------------ --------------- ---------- -------- -------
EMP_LIST EMP_LEADER 'PRESIDENT', 'MANAGER' 1 KN 10 DISABLED YES
EMP_LIST EMP_SALES 'SALESMAN' 2 KN 10 DISABLED YES
EMP_LIST EMP_OTH 'ANALYST', 'CLERK' 3 KN 10 DISABLED YES
KN @ ORCL>ALTER TABLE emp_list MODIFY DEFAULT ATTRIBUTES PCTFREE 15 NOLOGGING COMPRESS;
表已更改。
KN @ ORCL>SELECT table_name,
2 partitioning_type,
3 status,
4 def_tablespace_name,
5 def_pct_free,
6 def_logging,
7 def_compression
8 FROM user_part_tables -- 查询表的默认设置值
9 WHERE table_name = 'EMP_LIST';
TABLE_NAME PARTITI STATUS DEF_TABLESPACE_NAME DEF_PCT_FREE DEF_LOG DEF_COMP
--------------- ------- -------- ------------------------------ ------------ ------- --------
EMP_LIST LIST VALID KN 15 NO ENABLED
KN @ ORCL>alter table emp_list add partition emp_engineer values ('ENGINEER');
表已更改。
KN @ ORCL>SELECT table_name,
2 partition_name,
3 high_value,
4 partition_position,
5 tablespace_name,
6 pct_free,
7 compression,
8 logging
9 FROM user_tab_partitions
10 WHERE table_name = 'EMP_LIST';
TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION TABLESPACE_NAME PCT_FREE COMPRESS LOGGING
--------------- --------------- ------------------------- ------------------ --------------- ---------- -------- -------
EMP_LIST EMP_LEADER 'PRESIDENT', 'MANAGER' 1 KN 10 DISABLED YES
EMP_LIST EMP_SALES 'SALESMAN' 2 KN 10 DISABLED YES
EMP_LIST EMP_OTH 'ANALYST', 'CLERK' 3 KN 10 DISABLED YES
EMP_LIST EMP_ENGINEER 'ENGINEER' 4 KN 15 ENABLED NO
Modifying real attributes
Modify Partition
修改分区表/索引的真实属性（即已经创建好的分区表/索引）
而修改tablespace属性需要使用 alter table ptable move partitions tablespace old_ts to new_ts; 来移动表空间
ALTER TABLE ptable MODIFY PARTITION | SUBPARTITION ptable_pname ...;
可以修改hash分区表/hash子分区表的分配/释放extents、标记local index partition为UNUSABLE、重建已经标记为UNUSABLE的本地索引分区；
而对于range分区表/list分区表/list子分区表，则除了range分区表的可修改属性外，还可修改segments属性（tablespace除外）
对应的索引也可以使用类似的结构：
ALTER INDEX pindex MODIFY PARTITION pindex_pname ....;
ALTER TABLE sales MODIFY PARTITION sales_q1 STORAGE (MAXEXTENTS 10);
ALTER TABLE scubagear MODIFY PARTITION ts1 UNUSABLE LOCAL INDEXES;
ALTER TABLE dept MODIFY PARTITION p1 REBUILD UNUSABLE LOCAL INDEXES;
ALTER TABLE emp MODIFY SUBPARTITION p3_s1 REBUILD UNUSABLE LOCAL INDEXES;
给list分区增加分区值或者给range-list的list子分区增加分区值，在default的分区中不能含有此分区值的数据
此操作不会影响原有的索引
ALTER TABLE ptable MODIFY PARTITION ptable_list_pname ADD VALUES (...);
ALTER TABLE ptable MODIFY SUBPARTITION ptable_list_spname ADD VALUES (...);
KN @ ORCL>ALTER TABLE emp_list MODIFY PARTITION emp_engineer
2 ADD VALUES ('CHIEF ENGINEER','ENGINEER ASSISTANT');
ADD VALUES ('CHIEF ENGINEER','ENGINEER ASSISTANT')
*
第 2 行出现错误:
ORA-14036: 列的分区界限值过大 -- job列最多只能是9个字符
KN @ ORCL>desc emp_list;
名称 是否为空? 类型
------------------------------------------------------------------------ -------- ----------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
KN @ ORCL>ALTER TABLE emp_list MODIFY PARTITION emp_engineer
2 ADD VALUES ('CHIEF ENG','ENG ASSIS');
表已更改。
从list-partitioned table 中删除某个分区的分区值，或从range-list partitioned table中删除某个list子分区的分区值
在删除时，oracle会查询在此分区中是否存在此分区值的数据，如果有，则返回错误，不允许删除。
ALTER TABLE ptable MODIFY PARTITION ptable_list_pname DROP VALUES (...);
ALTER TABLE ptable MODIFY SUBPARTITION ptable_list_spname DROP VALUES (...);
Move Partition
Re-cluster data and reduce fragmentation
Move a partition to another tablespace
Modify create-time attributes
Store the data in compressed format using table compression
ALTER TABLE ptable MOVE PARTITION | SUBPARTITION ptable_pname
TABLESPACE new_ts [UPDATE INDEXES] [NOLOGGING] [COMPRESS]...;
KN @ ORCL>SELECT table_name,
2 partition_name,
3 high_value,
4 partition_position,
5 tablespace_name,
6 pct_free,
7 compression,
8 logging
9 FROM user_tab_partitions
10 WHERE table_name = 'EMP_LIST';
TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION TABLESPACE_NAME PCT_FREE COMPRESS LOGGING
--------------- --------------- ------------------------- ------------------ --------------- ---------- -------- -------
EMP_LIST EMP_LEADER 'PRESIDENT', 'MANAGER' 1 KN 10 DISABLED YES
EMP_LIST EMP_SALES 'SALESMAN' 2 KN 10 DISABLED YES
EMP_LIST EMP_OTH 'ANALYST', 'CLERK' 3 USERS 10 DISABLED YES
EMP_LIST EMP_ENGINEER 'ENGINEER', 'CHIEF ENG', 4 KN 15 ENABLED NO
'ENG ASSIS'
KN @ ORCL>ALTER TABLE emp_list MOVE PARTITION emp_oth TABLESPACE kn COMPRESS NOLOGGING;
表已更改。
KN @ ORCL>SELECT table_name,
2 partition_name,
3 high_value,
4 partition_position,
5 tablespace_name,
6 pct_free,
7 compression,
8 logging
9 FROM user_tab_partitions
10 WHERE table_name = 'EMP_LIST';
TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION TABLESPACE_NAME PCT_FREE COMPRESS LOGGING
--------------- --------------- ------------------------- ------------------ --------------- ---------- -------- -------
EMP_LIST EMP_LEADER 'PRESIDENT', 'MANAGER' 1 KN 10 DISABLED YES
EMP_LIST EMP_SALES 'SALESMAN' 2 KN 10 DISABLED YES
EMP_LIST EMP_OTH 'ANALYST', 'CLERK' 3 KN 0 ENABLED NO
EMP_LIST EMP_ENGINEER 'ENGINEER', 'CHIEF ENG', 4 KN 15 ENABLED NO
'ENG ASSIS'
Rename Partition
对分区/子分区重命名
ALTER TABLE | INDEX RENAME PARTITION | SUBPARTITION old_pname TO new_pname;
Split Partition
只能分割range/list分区或子分区
全局扫描被分割的分区数据，一笔笔的insert到新的分区中
如果分割后，其中一个分区不包含任何数据，则系统自动优化成像add partition一样
1. Range-Partitioned table
ALTER TABLE ptable SPLIT PARTITION ptable_pname AT (value)
INTO (PARTITION ptable_pname1 ..., PARTITION ptable_pname2 ...)
UPDATE INDEXES;
按照value值分割为= value的ptable_pname2两部分
如果不指定tablespace等存储参数，则与被分割的分区一致
KN @ ORCL>SELECT table_name,
2 partition_name,
3 high_value,
4 partition_position,
5 tablespace_name
6 FROM user_tab_partitions
7 WHERE table_name = 'EMP_RANGE';
TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION TABLESPACE_NAME
--------------- --------------- -------------------------------------------------- ------------------ ---------------
EMP_RANGE EMP_1980 TO_DATE(' 1981-01-01 00:00:00', 'SYYYY-MM-DD HH24: 1 KN
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE EMP_1981 TO_DATE(' 1982-01-01 00:00:00', 'SYYYY-MM-DD HH24: 2 KN
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE EMP_1982 TO_DATE(' 1983-01-01 00:00:00', 'SYYYY-MM-DD HH24: 3 KN
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE EMP_OTHERS MAXVALUE 4 USERS
KN @ ORCL>SELECT index_name,
2 partition_name,
3 high_value,
4 status,
5 tablespace_name
6 FROM user_ind_partitions
7 WHERE index_name LIKE 'EMP_RANGE_IDX%';
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS TABLESPACE_NAME
--------------- --------------- -------------------------------------------------- -------- ---------------
EMP_RANGE_IDX2 SYS_P41 USABLE KN
EMP_RANGE_IDX2 SYS_P42 USABLE KN
EMP_RANGE_IDX3 P1 USABLE KN
EMP_RANGE_IDX3 P2 USABLE KN
EMP_RANGE_IDX4 EMP_1980 TO_DATE(' 1981-01-01 00:00:00', 'SYYYY-MM-DD HH24: USABLE KN
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE_IDX4 EMP_1981 TO_DATE(' 1982-01-01 00:00:00', 'SYYYY-MM-DD HH24: USABLE KN
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE_IDX4 EMP_1982 TO_DATE(' 1983-01-01 00:00:00', 'SYYYY-MM-DD HH24: USABLE KN
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE_IDX4 EMP_OTHERS MAXVALUE USABLE USERS
已选择8行。
KN @ ORCL>select * from emp_range partition (emp_others);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7936 MASON SALESMAN 7698 16-3月 -85 3000 20 30
KN @ ORCL>ALTER TABLE emp_range
2 SPLIT PARTITION emp_others
3 AT (TO_DATE('1986/01/01','yyyy/mm/dd'))
4 INTO (PARTITION emp_1985,PARTITION emp_oth)
5 UPDATE INDEXES;
表已更改。
KN @ ORCL>SELECT table_name,
2 partition_name,
3 high_value,
4 partition_position,
5 tablespace_name
6 FROM user_tab_partitions
7 WHERE table_name = 'EMP_RANGE';
TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION TABLESPACE_NAME
--------------- --------------- -------------------------------------------------- ------------------ ---------------
EMP_RANGE EMP_1980 TO_DATE(' 1981-01-01 00:00:00', 'SYYYY-MM-DD HH24: 1 KN
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE EMP_1981 TO_DATE(' 1982-01-01 00:00:00', 'SYYYY-MM-DD HH24: 2 KN
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE EMP_1982 TO_DATE(' 1983-01-01 00:00:00', 'SYYYY-MM-DD HH24: 3 KN
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE EMP_1985 TO_DATE(' 1986-01-01 00:00:00', 'SYYYY-MM-DD HH24: 4 USERS
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE EMP_OTH MAXVALUE 5 USERS
KN @ ORCL>SELECT index_name,
2 partition_name,
3 high_value,
4 status,
5 tablespace_name
6 FROM user_ind_partitions
7 WHERE index_name LIKE 'EMP_RANGE_IDX%';
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS TABLESPACE_NAME
--------------- --------------- -------------------------------------------------- -------- ---------------
EMP_RANGE_IDX2 SYS_P41 USABLE KN
EMP_RANGE_IDX2 SYS_P42 USABLE KN
EMP_RANGE_IDX3 P1 USABLE KN
EMP_RANGE_IDX3 P2 USABLE KN
EMP_RANGE_IDX4 EMP_1980 TO_DATE(' 1981-01-01 00:00:00', 'SYYYY-MM-DD HH24: USABLE KN
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE_IDX4 EMP_1981 TO_DATE(' 1982-01-01 00:00:00', 'SYYYY-MM-DD HH24: USABLE KN
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE_IDX4 EMP_1982 TO_DATE(' 1983-01-01 00:00:00', 'SYYYY-MM-DD HH24: USABLE KN
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE_IDX4 EMP_1985 TO_DATE(' 1986-01-01 00:00:00', 'SYYYY-MM-DD HH24: USABLE USERS
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE_IDX4 EMP_OTH MAXVALUE USABLE USERS
已选择9行。
KN @ ORCL>select * from emp_range partition (emp_oth);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
KN @ ORCL>select * from emp_range partition (emp_1985);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7936 MASON SALESMAN 7698 16-3月 -85 3000 20 30
2. List-Partitioned table
ALTER TABEL ptable SPLIT PARTITION ptable_pname VALUES (value_list)
INTO (PARTITION ptable_pname1 ...,
PARTITION ptable_pname2 ...);
ptable_name中处于value_list中的部分分割至ptable_pname1，剩下的分割至ptable_pname2
3. Range-hash Partitioned table
只能分割range分区
与Range-Partitioned table的语法结构类似，只是在每个分区名后可以加上SUBPARTITION n指定hash子分区的数量，如果不指定，则与被分割的分区一致
4. Range-List Partitioned table
分割range分区时与1.Range-Partitioned table一样
分割list子分区：
ALTER TABLE sptable SPLIT PARTITION sptable_spname VALUES (value_list)
INTO
(SUBPARTITION sp_spname1 ...,
SUBPARTITION sp_spname2 ...
);
5. 索引，不能分割local索引，但可以分割全局索引，语法与range/list partitioned table一致，只需将TABLE改为INDEX
6. 加上UPDATE INDEXES可以尽量避免需要重建的索引数量，执行后检查是否有索引需要重建
7. 在每个分割后的分区名或子分区名后都可以加入一些存储参数，在语句最后可以加入PARALLEL n表示以n个并列进程来执行
Truncate Partition
删除分区中的数据，保留数据结构
ALTER TABLE ptable TRUNCATE PARTITION ptable_pname UPDATE INDEXES;
如果数据量小，则可以先DELETE，再TRUNCATE PARTITION，这样可以避免索引重建
如果数据量大，则可以先ALTER TABLE ptable TRUNCATE PARTITION ptable_pname; 再rebuild索引
如果数据量大且有外键引用，则可以先ALTER TABLE ptable DISABLE CONSTRAINT cons_name;再truncate partition；最后ENABLE CONSTRAINT
KN @ ORCL>ALTER TABLE emp_range TRUNCATE PARTITION emp_oth UPDATE INDEXES;
表被截断。
Drop Partitioned Table
DROP TABLE ptable PURGE;
Rebuild index
1. Global Index
ALTER INDEX pindex REBUILD PARTITION pindex_pname;
更好的是先DROP INDEX，再CREATE INDEX
2. LOCAL INDEX
ALTER INDEX pindex REBUILD PARTITION | SUBPARTITION pindex_pname; -- 针对每一个索引分区重建
ALTER TABLE ptable MODIFY PARTITION | SUBPARTITION ptable_pname REBUILD UNUSABLE LOCAL INDEXES;
-- 对ptable_pname分区上所有不可用的索引都重建
3. 在做ALTER TABLE动作时，尽量使用UPDATE INDEXES子句，避免标记索引为UNUSABLE
用于修改一个表、表分区的默认属性，在下一次创建分区、子分区时使用此默认属性。
1. 修改表的默认属性，hash分区表只能修改tablespace属性，分区将继承此属性：
ALTER TABLE ptable MODIFY DEFAULT ATTRIBUTES ....;
2. 修改分区的默认属性，子分区将继承此属性
ALTER TABLE ptable MODIFY DEFAULT ATTRIBUTES FOR PARTITION pname ...;
3. 分区索引与分区表类似的命令
例如: 修改表emp_list的属性：pctfree=15, nologging, compress
KN @ ORCL>SELECT table_name,
2 partition_name,
3 high_value,
4 partition_position,
5 tablespace_name,
6 pct_free,
7 compression,
8 logging
9 FROM user_tab_partitions
10 WHERE table_name = 'EMP_LIST';
TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION TABLESPACE_NAME PCT_FREE COMPRESS LOGGING
--------------- --------------- ------------------------- ------------------ --------------- ---------- -------- -------
EMP_LIST EMP_LEADER 'PRESIDENT', 'MANAGER' 1 KN 10 DISABLED YES
EMP_LIST EMP_SALES 'SALESMAN' 2 KN 10 DISABLED YES
EMP_LIST EMP_OTH 'ANALYST', 'CLERK' 3 KN 10 DISABLED YES
KN @ ORCL>ALTER TABLE emp_list MODIFY DEFAULT ATTRIBUTES PCTFREE 15 NOLOGGING COMPRESS;
表已更改。
KN @ ORCL>SELECT table_name,
2 partitioning_type,
3 status,
4 def_tablespace_name,
5 def_pct_free,
6 def_logging,
7 def_compression
8 FROM user_part_tables -- 查询表的默认设置值
9 WHERE table_name = 'EMP_LIST';
TABLE_NAME PARTITI STATUS DEF_TABLESPACE_NAME DEF_PCT_FREE DEF_LOG DEF_COMP
--------------- ------- -------- ------------------------------ ------------ ------- --------
EMP_LIST LIST VALID KN 15 NO ENABLED
KN @ ORCL>alter table emp_list add partition emp_engineer values ('ENGINEER');
表已更改。
KN @ ORCL>SELECT table_name,
2 partition_name,
3 high_value,
4 partition_position,
5 tablespace_name,
6 pct_free,
7 compression,
8 logging
9 FROM user_tab_partitions
10 WHERE table_name = 'EMP_LIST';
TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION TABLESPACE_NAME PCT_FREE COMPRESS LOGGING
--------------- --------------- ------------------------- ------------------ --------------- ---------- -------- -------
EMP_LIST EMP_LEADER 'PRESIDENT', 'MANAGER' 1 KN 10 DISABLED YES
EMP_LIST EMP_SALES 'SALESMAN' 2 KN 10 DISABLED YES
EMP_LIST EMP_OTH 'ANALYST', 'CLERK' 3 KN 10 DISABLED YES
EMP_LIST EMP_ENGINEER 'ENGINEER' 4 KN 15 ENABLED NO
Modifying real attributes
Modify Partition
修改分区表/索引的真实属性（即已经创建好的分区表/索引）
而修改tablespace属性需要使用 alter table ptable move partitions tablespace old_ts to new_ts; 来移动表空间
ALTER TABLE ptable MODIFY PARTITION | SUBPARTITION ptable_pname ...;
可以修改hash分区表/hash子分区表的分配/释放extents、标记local index partition为UNUSABLE、重建已经标记为UNUSABLE的本地索引分区；
而对于range分区表/list分区表/list子分区表，则除了range分区表的可修改属性外，还可修改segments属性（tablespace除外）
对应的索引也可以使用类似的结构：
ALTER INDEX pindex MODIFY PARTITION pindex_pname ....;
ALTER TABLE sales MODIFY PARTITION sales_q1 STORAGE (MAXEXTENTS 10);
ALTER TABLE scubagear MODIFY PARTITION ts1 UNUSABLE LOCAL INDEXES;
ALTER TABLE dept MODIFY PARTITION p1 REBUILD UNUSABLE LOCAL INDEXES;
ALTER TABLE emp MODIFY SUBPARTITION p3_s1 REBUILD UNUSABLE LOCAL INDEXES;
给list分区增加分区值或者给range-list的list子分区增加分区值，在default的分区中不能含有此分区值的数据
此操作不会影响原有的索引
ALTER TABLE ptable MODIFY PARTITION ptable_list_pname ADD VALUES (...);
ALTER TABLE ptable MODIFY SUBPARTITION ptable_list_spname ADD VALUES (...);
KN @ ORCL>ALTER TABLE emp_list MODIFY PARTITION emp_engineer
2 ADD VALUES ('CHIEF ENGINEER','ENGINEER ASSISTANT');
ADD VALUES ('CHIEF ENGINEER','ENGINEER ASSISTANT')
*
第 2 行出现错误:
ORA-14036: 列的分区界限值过大 -- job列最多只能是9个字符
KN @ ORCL>desc emp_list;
名称 是否为空? 类型
------------------------------------------------------------------------ -------- ----------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
KN @ ORCL>ALTER TABLE emp_list MODIFY PARTITION emp_engineer
2 ADD VALUES ('CHIEF ENG','ENG ASSIS');
表已更改。
从list-partitioned table 中删除某个分区的分区值，或从range-list partitioned table中删除某个list子分区的分区值
在删除时，oracle会查询在此分区中是否存在此分区值的数据，如果有，则返回错误，不允许删除。
ALTER TABLE ptable MODIFY PARTITION ptable_list_pname DROP VALUES (...);
ALTER TABLE ptable MODIFY SUBPARTITION ptable_list_spname DROP VALUES (...);
Move Partition
Re-cluster data and reduce fragmentation
Move a partition to another tablespace
Modify create-time attributes
Store the data in compressed format using table compression
ALTER TABLE ptable MOVE PARTITION | SUBPARTITION ptable_pname
TABLESPACE new_ts [UPDATE INDEXES] [NOLOGGING] [COMPRESS]...;
KN @ ORCL>SELECT table_name,
2 partition_name,
3 high_value,
4 partition_position,
5 tablespace_name,
6 pct_free,
7 compression,
8 logging
9 FROM user_tab_partitions
10 WHERE table_name = 'EMP_LIST';
TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION TABLESPACE_NAME PCT_FREE COMPRESS LOGGING
--------------- --------------- ------------------------- ------------------ --------------- ---------- -------- -------
EMP_LIST EMP_LEADER 'PRESIDENT', 'MANAGER' 1 KN 10 DISABLED YES
EMP_LIST EMP_SALES 'SALESMAN' 2 KN 10 DISABLED YES
EMP_LIST EMP_OTH 'ANALYST', 'CLERK' 3 USERS 10 DISABLED YES
EMP_LIST EMP_ENGINEER 'ENGINEER', 'CHIEF ENG', 4 KN 15 ENABLED NO
'ENG ASSIS'
KN @ ORCL>ALTER TABLE emp_list MOVE PARTITION emp_oth TABLESPACE kn COMPRESS NOLOGGING;
表已更改。
KN @ ORCL>SELECT table_name,
2 partition_name,
3 high_value,
4 partition_position,
5 tablespace_name,
6 pct_free,
7 compression,
8 logging
9 FROM user_tab_partitions
10 WHERE table_name = 'EMP_LIST';
TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION TABLESPACE_NAME PCT_FREE COMPRESS LOGGING
--------------- --------------- ------------------------- ------------------ --------------- ---------- -------- -------
EMP_LIST EMP_LEADER 'PRESIDENT', 'MANAGER' 1 KN 10 DISABLED YES
EMP_LIST EMP_SALES 'SALESMAN' 2 KN 10 DISABLED YES
EMP_LIST EMP_OTH 'ANALYST', 'CLERK' 3 KN 0 ENABLED NO
EMP_LIST EMP_ENGINEER 'ENGINEER', 'CHIEF ENG', 4 KN 15 ENABLED NO
'ENG ASSIS'
Rename Partition
对分区/子分区重命名
ALTER TABLE | INDEX RENAME PARTITION | SUBPARTITION old_pname TO new_pname;
Split Partition
只能分割range/list分区或子分区
全局扫描被分割的分区数据，一笔笔的insert到新的分区中
如果分割后，其中一个分区不包含任何数据，则系统自动优化成像add partition一样
1. Range-Partitioned table
ALTER TABLE ptable SPLIT PARTITION ptable_pname AT (value)
INTO (PARTITION ptable_pname1 ..., PARTITION ptable_pname2 ...)
UPDATE INDEXES;
按照value值分割为= value的ptable_pname2两部分
如果不指定tablespace等存储参数，则与被分割的分区一致
KN @ ORCL>SELECT table_name,
2 partition_name,
3 high_value,
4 partition_position,
5 tablespace_name
6 FROM user_tab_partitions
7 WHERE table_name = 'EMP_RANGE';
TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION TABLESPACE_NAME
--------------- --------------- -------------------------------------------------- ------------------ ---------------
EMP_RANGE EMP_1980 TO_DATE(' 1981-01-01 00:00:00', 'SYYYY-MM-DD HH24: 1 KN
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE EMP_1981 TO_DATE(' 1982-01-01 00:00:00', 'SYYYY-MM-DD HH24: 2 KN
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE EMP_1982 TO_DATE(' 1983-01-01 00:00:00', 'SYYYY-MM-DD HH24: 3 KN
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE EMP_OTHERS MAXVALUE 4 USERS
KN @ ORCL>SELECT index_name,
2 partition_name,
3 high_value,
4 status,
5 tablespace_name
6 FROM user_ind_partitions
7 WHERE index_name LIKE 'EMP_RANGE_IDX%';
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS TABLESPACE_NAME
--------------- --------------- -------------------------------------------------- -------- ---------------
EMP_RANGE_IDX2 SYS_P41 USABLE KN
EMP_RANGE_IDX2 SYS_P42 USABLE KN
EMP_RANGE_IDX3 P1 USABLE KN
EMP_RANGE_IDX3 P2 USABLE KN
EMP_RANGE_IDX4 EMP_1980 TO_DATE(' 1981-01-01 00:00:00', 'SYYYY-MM-DD HH24: USABLE KN
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE_IDX4 EMP_1981 TO_DATE(' 1982-01-01 00:00:00', 'SYYYY-MM-DD HH24: USABLE KN
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE_IDX4 EMP_1982 TO_DATE(' 1983-01-01 00:00:00', 'SYYYY-MM-DD HH24: USABLE KN
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE_IDX4 EMP_OTHERS MAXVALUE USABLE USERS
已选择8行。
KN @ ORCL>select * from emp_range partition (emp_others);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7936 MASON SALESMAN 7698 16-3月 -85 3000 20 30
KN @ ORCL>ALTER TABLE emp_range
2 SPLIT PARTITION emp_others
3 AT (TO_DATE('1986/01/01','yyyy/mm/dd'))
4 INTO (PARTITION emp_1985,PARTITION emp_oth)
5 UPDATE INDEXES;
表已更改。
KN @ ORCL>SELECT table_name,
2 partition_name,
3 high_value,
4 partition_position,
5 tablespace_name
6 FROM user_tab_partitions
7 WHERE table_name = 'EMP_RANGE';
TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION TABLESPACE_NAME
--------------- --------------- -------------------------------------------------- ------------------ ---------------
EMP_RANGE EMP_1980 TO_DATE(' 1981-01-01 00:00:00', 'SYYYY-MM-DD HH24: 1 KN
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE EMP_1981 TO_DATE(' 1982-01-01 00:00:00', 'SYYYY-MM-DD HH24: 2 KN
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE EMP_1982 TO_DATE(' 1983-01-01 00:00:00', 'SYYYY-MM-DD HH24: 3 KN
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE EMP_1985 TO_DATE(' 1986-01-01 00:00:00', 'SYYYY-MM-DD HH24: 4 USERS
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE EMP_OTH MAXVALUE 5 USERS
KN @ ORCL>SELECT index_name,
2 partition_name,
3 high_value,
4 status,
5 tablespace_name
6 FROM user_ind_partitions
7 WHERE index_name LIKE 'EMP_RANGE_IDX%';
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS TABLESPACE_NAME
--------------- --------------- -------------------------------------------------- -------- ---------------
EMP_RANGE_IDX2 SYS_P41 USABLE KN
EMP_RANGE_IDX2 SYS_P42 USABLE KN
EMP_RANGE_IDX3 P1 USABLE KN
EMP_RANGE_IDX3 P2 USABLE KN
EMP_RANGE_IDX4 EMP_1980 TO_DATE(' 1981-01-01 00:00:00', 'SYYYY-MM-DD HH24: USABLE KN
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE_IDX4 EMP_1981 TO_DATE(' 1982-01-01 00:00:00', 'SYYYY-MM-DD HH24: USABLE KN
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE_IDX4 EMP_1982 TO_DATE(' 1983-01-01 00:00:00', 'SYYYY-MM-DD HH24: USABLE KN
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE_IDX4 EMP_1985 TO_DATE(' 1986-01-01 00:00:00', 'SYYYY-MM-DD HH24: USABLE USERS
MI:SS', 'NLS_CALENDAR=GREGORIA
EMP_RANGE_IDX4 EMP_OTH MAXVALUE USABLE USERS
已选择9行。
KN @ ORCL>select * from emp_range partition (emp_oth);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
KN @ ORCL>select * from emp_range partition (emp_1985);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7936 MASON SALESMAN 7698 16-3月 -85 3000 20 30
2. List-Partitioned table
ALTER TABEL ptable SPLIT PARTITION ptable_pname VALUES (value_list)
INTO (PARTITION ptable_pname1 ...,
PARTITION ptable_pname2 ...);
ptable_name中处于value_list中的部分分割至ptable_pname1，剩下的分割至ptable_pname2
3. Range-hash Partitioned table
只能分割range分区
与Range-Partitioned table的语法结构类似，只是在每个分区名后可以加上SUBPARTITION n指定hash子分区的数量，如果不指定，则与被分割的分区一致
4. Range-List Partitioned table
分割range分区时与1.Range-Partitioned table一样
分割list子分区：
ALTER TABLE sptable SPLIT PARTITION sptable_spname VALUES (value_list)
INTO
(SUBPARTITION sp_spname1 ...,
SUBPARTITION sp_spname2 ...
);
5. 索引，不能分割local索引，但可以分割全局索引，语法与range/list partitioned table一致，只需将TABLE改为INDEX
6. 加上UPDATE INDEXES可以尽量避免需要重建的索引数量，执行后检查是否有索引需要重建
7. 在每个分割后的分区名或子分区名后都可以加入一些存储参数，在语句最后可以加入PARALLEL n表示以n个并列进程来执行
Truncate Partition
删除分区中的数据，保留数据结构
ALTER TABLE ptable TRUNCATE PARTITION ptable_pname UPDATE INDEXES;
如果数据量小，则可以先DELETE，再TRUNCATE PARTITION，这样可以避免索引重建
如果数据量大，则可以先ALTER TABLE ptable TRUNCATE PARTITION ptable_pname; 再rebuild索引
如果数据量大且有外键引用，则可以先ALTER TABLE ptable DISABLE CONSTRAINT cons_name;再truncate partition；最后ENABLE CONSTRAINT
KN @ ORCL>ALTER TABLE emp_range TRUNCATE PARTITION emp_oth UPDATE INDEXES;
表被截断。
Drop Partitioned Table
DROP TABLE ptable PURGE;
Rebuild index
1. Global Index
ALTER INDEX pindex REBUILD PARTITION pindex_pname;
更好的是先DROP INDEX，再CREATE INDEX
2. LOCAL INDEX
ALTER INDEX pindex REBUILD PARTITION | SUBPARTITION pindex_pname; -- 针对每一个索引分区重建
ALTER TABLE ptable MODIFY PARTITION | SUBPARTITION ptable_pname REBUILD UNUSABLE LOCAL INDEXES;
-- 对ptable_pname分区上所有不可用的索引都重建
3. 在做ALTER TABLE动作时，尽量使用UPDATE INDEXES子句，避免标记索引为UNUSABLE
喜欢
0
赠金笔