Oracle Partition维护之 - tabel / index分区命令

今天在看CBO书的时候看到跨子分区表查询的执行计划时，对分区表的partition exchange loading，查询到此片博文，转来使用

分区表维护的常用命令：

ALTER TABLE -- DROP -- PARTITION -- ADD | -- RENAME | -- MODIFITY | -- TRUNCATE | -- SPILT | -- MOVE | -- EXCHANGE |

分区索引的常用维护命令：

ALTER INDEX -- DROP -- PARTITION -- REBUILD | -- RENAME | -- MODIFITY | -- SPILT | -- PARALLEL -- UNUSABLE

 

1、ALTER TABLE DROP PARTITION
用于删除table中某个 PARTITION和其中的数据，主要是用于历史数据的删除。如果还想保留数据，就需要合并到另一个partition中。
删除该 partition之后，如果再insert该partition范围内的值，要存放在更高的partition中。如果你删除了最大的 partition，就会出错。
删除table partition的同时，删除相应的local index。即使该index是IU状态。
如 果table上有global index，且该partition不空，drop partition会使所有的global index 为IU状态。如果不想REBUIL INDEX，可以用SQL语句手工删除数据，然后再DROP PARTITION .
例子：

ALTR ATBEL sales DROP PARTITION dec96;

到底是DROP PARTITION或者是DELETE？
如果GLOBAL INDEX是最重要的，就应该先DELETE 数据再DROP PARTITION。
在下面情况下，手工删除数据的代价比DROP PARTITION要小
- 如果要删除的数据只占整个TABLE的小部分
- 在TABLE中有很多的GLOBAL INDEX。

在下面情况下，手工删除数据的代价比DROP PARTITION要大
- 如果要删除的数据占整个TABLE的绝大部分
- 在TABLE中没有很多的GLOBAL INDEX。

如果在TABLE是父TABLE，有被引用的约束，且PARTITION不空，DROP PARTITION时出错。
如果要删除有数据的 PARTITION，应该先删除引用约束。或者先DELETE,然后再DROP PARTITION。
如果TABLE只有一个PARTITON, 不能DROP PARTITION，只能DROP TABLE。

2、ALTER INDEX .. DROP PARTITION
删除PARTIOTN GLOBAL INDEX上删除INDEX和INDEX ENTRY，一般用于平衡I/O。
INDEX必须是GLOBAL INDEX。不能显式的drop local index partition，不能删除最大的index。
删除之后，insert属于该 partition的值时候，index建立在更高的partition。
如果包含数据的partition删除之后，下一个partition 是IU状态，必须rebuild。可以删除IU状态的partition，即使它包含数据。

3、ALTER TABLE / INDEX RENAME PARTITION
主要用于改变隐式建立 的INDEX NAME。
INDEX 可以是IU状态。
一般的INDEX可以用ALTER INDEX RENAME ....

4、ALTER TABLE .. ADD PARTITION...
只能加到最后一个 PARTITION之后。一般用于数据会单调增长的地方，比如每周/月/年会增加新的历史数据等。
SPLIT可以在中间插入PARTITION。
如 果VALUES LESS THAN的第一个值是MAXVALUE，就不能增加PARTITION.必须SPLIT。
该命令也可以给自动增加 PARTITION LOCAL INDEX。新的LOCAL INDEX PARTITION名字和TABLE PARTITION一致。新的LOCAL INDEX PARTITION使用前一个INDEX PARTITION的缺省值，存放在TABLE PARTITION同样的TABLESPACE。
不影响GLOBAL INDEX。
即使TABLE有INDEX或者INDEX PARTITION是IU状态也可以增加PARTITION.

5、ALTER TABLE/INDEX MODIFY PARTITION
1）ALTER TABLE MODIFY PARTITION
修改PARTITION的物理属性，比如分配更多的EXTEND。
如果要移动到新的 TABLESPACE，或者改变CREATE建立的属性，就需要ALTER TABLE MOVE PARTITION。

2）ALTER INDEX MODIFY PARTITION
修改INDEX的物理属性。
可以增减更多的EXTENT
必 须是GLOBAL/LOCAL PARTITION INDEX。
ALTER TABLE/INDEX ... MODIFY PARTITION ... UNUSABLE。
如果要把UNUSABLE变成USABLE，
- REBUILD INDEX PARTITION
- DROP + RECREATE 包含这个PARTITION的INDEX。

如果修改TABLE TABLE ... 的物理属性，值放在数据字典，只有ADD PARTITION的时候才使用。不会改变现有的PARTITION的属性。
比如:ALTER TABLE sales PCTFREE 0 PCTUSED 20.

ALTER INDEX直接修改PARTITION和NONPARTITION的物理属性。
如果修改PARTITION INDEX的物理属性，也是值放在数据字典，只有建立新的INDEXPARTITION的时候才使用。不会改变现有的PARTITION的属性。
如 果INDEX是GLOBAL的，在ALTER INDEX SPLIT PARTITION的时候用到。如果是LOCAL INDEX，在隐式的增加INDEX PARTITION的时候用到，比如ALTER TABLE ADD PARTITION或者SPLIT PARTITION。这样就可以控制ALTER TABLE建立LOCAL INDEX的属性了。
如果INDEX是NONPARTITION的， 标记为INDEX UNUSABLE ，不允许ALTER 命令。只能在RECREATE的时候设置其属性。

6、ALTER TABLE MODIFY PARTITION UNUSABLE LOCAL INDEXES
把 TABLE所有相关的LOCAL INDEX设置为UNUSABLE。用于要进行大规模的DML操作的时候。
UNUSABLE-->USABLE 的方法：
- ALTER INDEX REBUILD PARTITION
- ALTER TABLE MODIFY PARTITION REBUILD UNUSABLE LOCAL INDEXES
可以查询DBA/ALL/USER_PARTITIONS看 INDEX的状态。

7、ALTER TABLE MODIFY PARTITION REBUILD LOCAL INDEXES
REBUILD 该TABLE上所有不可用的LOCAL INDEX。

8、ALTER INDEX ... UNUSABLE
- 可以对PARTITION/NONPARTITION INDEX。
- 可以使NONPARTITION INDEX 为不可用状态。
- 可以使所有的INDEX PARTITION为不可用状态。
- 处于IU状态的NOPARTITION INDEX必须REBUILD，或者DROP+RECREATE。
- 一次只能REBUILD一个PARTITION INDEX。
- 对处于不可用状态的GLOBAL INDEX ，DROP+RECREATE的效率要高于REBUILD。

9、ALTER INDEX ... REBUILD PARTITION...
用于REBUILD INDEX的一个PARTITION，如果不需要RECREATE一个大的INDEX，用这个命令修复之。
也可以用于把一个INDEX PARTITION移动到另外的TABLESPACE，或者改变CREATE时候的物理参数，或者作为SPLIT操作的最后一步。
并行 rebuild:
- 如果rebuild的时候指定parallel，则使用之；
- 否则使用index缺省的parallel属性；
- 否则使用table缺省的parallel属性；
- 否则不使用并行。

10、alter session set skip_unusable_indexes
允许用户在有 unusable index或者index partition的table上进行DML操作。否则就会产生错误。用在进行大规模的修改和加载数据的时候，推迟index的维护。
但是如果 query指定不可用的index或者index partition，依然会报错。
不能跳过对不可用的唯一索引的维护。

11、alter table split partition
建立两个新的partition，有自 己新的segment，新的物理属性，和initial extent。原来partition的segment都丢弃。
用在如果 partition太大，导致备份、恢复和维护操作时间很长，可以考虑使用split tablespace。
也可以用在重新分布I/O负载。
在split partition的时候，同样建立相应的local index。
如果在split的时候出现问题，新的segment就删除，语句 rollback。
index即使是不可用的，index partition也可以split。
例子：
ALTER TABLE parts SPLIT PARTITION depot4
AT('40-001') INTO
( PARTITION depot4 TABLESPACE ts009 MINEXTENTS 2，
PARTITION depot9 TABLESPACE ts010
);

原来的index partition缺省的 物理属性用于新的local index partition，存放在table partition的tablespace里。除非已经定义了tablespace。
新分离出来的包含数据的index partition被设置为不可用，空的index partition的index是valid的。

12、alter index split partition
把global index的一个partition分为两个partition。注意必须是global的，不能自己来split local index。
建 立新的index segment，不再使用原来的空间。
如果是切分不可用的index partition，则新的index partition都是不可用的。必须rebuild。
如果index partition包含数据，则新的partition都是不可用的。

13、alter table move partition
删除旧的数据segment，建立新的 segment，即使没有指定新的tablespace。
用于把数据移动到其他的partition，重新组织数据减少碎片，或者改变物理属性。
如 果指定了partition 名字，则move partition之后，影响所有的index为不可用。包括
- 所有global index partition
- 每个local index的相应partition，但是它们的tablespace属性不变。
并行度：
如 果在move中指定，则使用之，
否则使用table缺省的并行设置，
否则就不使用并行了。
但是要注意的是move命令中的 parallel不改变table本身的parallel设置。
如果使用NOLOGGING，这个PARTITION应该周期性的备份。

14、ALTER TABLE EXCHANGE PARTITION
可以把非分区的TABLE和分区 的数据交换。
这个过程是双向的。
实际上不交换数据。
在数据字典进行更改。
这个TABLE必须是存在的，不能是 PARTITION TABLE或者是CLUSTER TABLE。
用户必须对两个表有ALTER 权限。
这两个TABLE不能有任何约 束。
不激活任何TRIGGER。
这两个PT和T必须有相同的结构：相同的CLOUMN，相同的CLOUMN类型和大小。
影响到它 们的GLOBAL INDEX。

例子：
ALTER TABLE sales EXCHANGE PARTITION feb97 WITH TABLE sales_feb97;

可以带的参数是
WITH VALIDATION：检查sales_feb97表，如果有问题返回错误。
WITHOUT VALIDATION：不检查TABLE sales_feb97，由用户自己检查。
iINCLUDING INDEXES：交换它们的index，其中的index必须相同的类型。
EXCLUING INDEXES：相关的INDEX都是不可用的。

TABLE和PARTITION的相关统计信息也交换，包括TABLE,CLUMON,INDEX统计和直方图。PARTITION TABLE的总体信息要重新统计。

它们的LOG属性也交换。

15、修改table的逻辑属性
比如增加新的column， 约束，改变cloumn的类型，或者enable约束。如果是partition table，这些属性是针对所有的partition的。
改变 逻辑属性的规则：
- 不能改变用作table partition key的cloumn类型、长度。
- 不能改变用作index partition key的cloumn类型、长度。
- 不能添加LONG, LONG RAW
- 不能把列改为LONG, LONG RAW
- 对有PARTITION在只读TABLESPACE上的TABLE，新的列不能有缺省值
- 对有PARTITION在只读TABLESPACE上的TABLE，不能从VARCHAR,VARCHAR2改为CHAR
- 对有PARTITION在只读TABLESPACE上的TABLE，不能增加CHAR的长度。

如果要增加唯一索引/PK，ORACLE会做相应的操作：
- 如果在这些COLUMN上已经有唯一索引，则使用之。
- 如果有了非唯一索引，则返回错误
- 如果已经有了唯一索引，但是是不可用的，则返回错误
- 否则，ORACLE建立GLOBAL NOPARTITION INDEX.

16、ALTER TABLE ..TRUNCATE PARTITION
删除PARTITION中的 所有数据，比DELETE快。
同时删除对应的LOCAL INDEX数据，即使是不可用的INDEX。同时那些不可用的INDEX设置为VALID。INDEX的空间是释放还是等待再使用，取决于TABLE PARTITION的DROP STORAGE或者REUSE STORAGE。
如果有GLOBAL INDEX,且PARTITION包含数据，则它就变成不可用的了。如果想避免这样，可以先DELETE数据，再TRUNCATE PARTITION。
如 果TABLE被其他表引用，且PARTITION不空，则返回错误。你可以先DISABLE约束，或者先DELETE再TRUNCATE。
不激发 TRIGGER.

17、ALTER INDEX .. PARALLEL
改变INDEX的并行属性。

以下操作需要ALTER权限和DROP ANY TABLE的权限
- ALTER TABLE DROP PARTITION
- ALTER TABLE TRUNCATE PARTITION

以下操作需要ALTER权限和在TABLESPACE上的空间分配权限
- ALTER INDEX MODIFY PARTITION
- ALTER INDEX REBUILD PARTITION
- ALTER INDEX SPLIT PARTITION
- ALTER TABLE ADD PARTITION
- ALTER TABLE SPLIT PARTITION
- ALTER TABLE MODIFY PARTITION
- ALTER TABLE MOVE PARTITION

19、相关的数据字典
USER/ALL/DBA_PART_TABLES
USER/ALL/DBA_PART_TINDEXES
USER/ALL/DBA_PART_KEY_COLUMNS
USER/ALL/DBA_TAB_PARTITIONS
USER/ALL/DBA_IND_PARTITIONS
USER/ALL/DBA_PART_COL_STATISTICS
USER/ALL/DBA_TAB_COL_STATISTICS
USER/ALL/DBA_PART_HISTOGRAMS
USER/ALL/DBA_TAB_HISTOGRAMS
USER/ALL/DBA_OBJECTS
USER/ALL/DBA_TABLES
USER/ALL/DBA_INDEXES
USER/ALL/DBA_TAB_COLUMNS

20、PLAN_TABLE中的新COLUMN
PARTITION_START
PARTITION_STOP
PARTITION_ID
在 分析步骤中加了新的步骤：PARTITION
另外在TABLE/INDEX的存取步骤中有引用PARTITION的步骤。

PARTITION_START和PARTITION_STOP
确定开始/结束的PARTITION范围
值包括：
NUMBER(n): 由SQL编译器认定的第N个PARTITION
KEY:从某个PARTITION KEY值开始的开始时间
ROW LOCATION:从某行开始/结束的时间
INVALID:存取的PARTITION范围是空的。

PARTITION ID：存放开始结束的PARTITION 值对。

OPTION列：

对PARTITION步骤时，可以有CONCATENATED/SINGLE/EMPTY
CONCATENATED:合并存取的 PARTITION结果集合
SINGLE：指示在运行时是单个PARTITION
EMPTY:存取的PARTITION是空的

对TABLE存取时候，按照ROWID范围确定，可以有如下值：
BY USER ROWID
BY INDEX ROWID
BU GLOBAL INDEX ROWID
BY LOCAL INDEX ROWID

21、常规路径的SQL*Loader
可以对一个partition table使用常规路径的SQL*Loader，没有新增语法，使用insert语句，同时更新local/global index。可以同时对一个table进行多个load。
可以一次load一个partition的数据，必须在load的控制文件里面指定 table和partition。不属于该partition的数据badfile中。

22、直接路径的sql*loader
没有增加新的语法。
index自动更新。
指定 table和partition name，DIRECT=TRUE
如果你在LOAD整个TABLE，不能同时运行其他的LOAD
如果没 有GLOBAL INDEX，可以在不同的PARTITION上运行LOAD。
也可以对一个partition进行并行直接load：
- 必须指定PARALLEL=TRUE
相关的LOCAL INDEX PARTITION设置为不可用，必须自己重建。
不能有 GLOBAL INDEX
可以并发的在一个TABLE上对不同的PARTITION进行直接路径LOAD。

23、EXPORT
依然支持FULL/USER/TABLE
PARTITION只支持 TABLE方式
必须指定TABLE:PARTITION

24、IMPORT
可以把从PARTITION/NONPARTITION TABLE中DUMP出来的文件中，IMPORT到分区或者不分区的文件中。
支持FULL/TABLE/USER
如果原来的TABLE是 PARTITION的，IMPORT建立PARTITION TABLE。
所有高于现在的PARTITION TABLE最高KEY的值都会被拒绝。
必须指定TABLE:PARTITION。
可以设置SKIP_UNUSABLE_INDEXES， 跳过不可以用的INDEX.

25、ALALYZE
分析的目标可以是单个PARTITION，整个TABLE或者INDEX。
分 析TABLE,INDEX,COLUMN的统计信息，并合并在一起。但是不合并它们的HISTOGRAM。
ORACLE优化器发现相关 PARTITION没有被分析，使用缺省的TABLE/INDEX。