- 浏览: 70574 次
- 性别:
- 来自: 杭州
文章分类
最新评论
分区表维护的常用命令:
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。
发表评论
-
关于Oracle 版本
2015-10-10 10:23 0第一部分是“Version Number",也就是产 ... -
了解Oracle数据库的版本号
2015-10-10 10:20 0Major Database Release ... -
PDF 资料
2013-03-13 15:45 0Java design pattern --Bob ... -
Oracle sys和system用户、sysdba 和sysoper系统权限、sysdba和dba角色的区别 [转]
2013-03-12 14:17 1022sys和system用户区别 1)最重要的区别,存储的数 ... -
Oracle 用户、对象权限、系统权限 [转]
2013-03-12 14:12 0--============================ ... -
表分区分割脚本
2013-03-12 13:10 728表分区分割脚本 -
Oracle Session 视图[转]
2013-03-06 10:17 976v$session v$session_wait v$ ... -
10G中查看历史执行计划信息[转]
2013-03-01 11:02 3761现在总结下10G的,使用的是AWR报告中的信息,主要是查询 ... -
Oracle 表连接 [转]
2013-02-26 15:20 647Oracle 表之间的连接分为三种: 1. 内连接(自然 ... -
oracle的number类型精度、刻度范围 [转]
2013-02-26 15:06 5250一、 oracle 的 number 类型精度、刻度范围 ... -
Oracle Tablespace
2012-11-29 16:53 01. 几个重要的TableSpace SYSTE ... -
[转]Optimizing SPLIT PARTITION and SPLIT SUBPARTITION Operations
2012-11-27 15:11 911Optimizing SPLIT PARTITION and ... -
Oracle splitting partitions简单小结[转]
2012-11-27 15:12 995http://www.oracleonlinux.cn/201 ... -
When the explanation doesn't sound quite right
2012-10-30 13:05 0When the explanatio ... -
oracle中join的用法 .
2012-10-10 11:43 0oracle中join的用法8i: create ... -
[转]Oracle中Left Outer Join和外关联(+)的区别
2012-11-27 15:15 840外关联是Oracle数据库的专有语句 Left Outer ... -
[转]关于ORACLE的锁表与解锁总结
2012-09-29 11:11 0总结1:Oracle的锁表与解锁 selects.userna ... -
not in/not exists 的 null 陷阱
2012-09-27 11:07 0[转]not in/not exists 的 nul ... -
Oracle Database Link Tutorials,Examples to create, use, manage and drop them[转]
2012-09-21 10:54 0Oracle Database Link TutorialsE ... -
Understanding Oracle QUERY PLAN
2012-01-06 11:28 1160Understanding Oracle QUERY PLAN ...
相关推荐
### Oracle Partition 分区详解 #### 一、表空间与分区表概念 在Oracle数据库中,**表空间**是数据文件的逻辑容器,用于组织和管理数据。它由一个或多个数据文件组成,其中包含了数据库的所有数据对象,如表、索引...
Oracle Partition 是一种数据库优化技术,它将大型表和索引分解为更小、更易管理的部分,称为分区。这一特性旨在解决支持非常大的表和索引时所面临的挑战。通过分区,可以对数据库对象进行有效的管理和维护,同时...
- **添加分区**:当需要添加新的分区时,可以使用`ALTER TABLE`命令添加新的分区。 - **删除分区**:如果不再需要某个分区,可以使用`ALTER TABLE`命令将其删除。 - **移动分区**:可以通过`ALTER TABLE`命令将分区...
### Oracle表分区详解 #### 1. 表空间及分区表的概念 - **表空间**: - **定义**:表空间是Oracle数据库中的逻辑存储单元,由一个或多个数据文件组成,用于存储数据库对象(如表、索引等)。在逻辑上,表空间为...
### Oracle分区表(Partition Table)的创建及管理 #### 一、创建分区表 在Oracle数据库中,分区表是一种非常实用的技术,它能够通过将一个大表分割成多个更小的部分来提高查询性能和可管理性。根据不同的数据分布...
【Oracle分区表用法】 Oracle分区表是一种高级的数据库管理技术,它将大型表的数据分散存储在不同的物理区域,以提升查询效率和系统的整体性能。本文将详细介绍分区表的概念、作用、优缺点,以及各种类型的分区表...
从提供的文件信息中,我们可以提取出关于Oracle分区表的相关知识点。这些知识点主要涉及分区表的概念、类型以及它们在数据管理和查询性能提升方面的作用。 首先,分区表是数据库中用于数据组织的一种机制。在Oracle...
标题中提到的“Oracle 分区表自动维护脚本”是指一种在Oracle数据库系统中用于管理和维护分区表的自动化脚本。这种脚本可以执行多个维护任务,包括但不限于:增加新分区、删除历史分区、拆分分区和数据清除等操作。...
### Oracle表分区转换详解 #### 一、背景与目的 在数据库管理中,表分区是一种重要的数据组织方式,它能够显著提高大型表的查询性能,简化数据管理,并有助于提高系统的可维护性和可用性。有时,我们需要将非分区...
扩展Hash分区表通常通过`ADD PARTITION`命令进行。增加新分区时,需要根据哈希算法将原有数据分配到新分区。例如,当有100个分区,要增加第101个分区时,根据2的整数幂规则,M为128,那么新分区的数据来源于第37分区...
### Oracle 分区表与分区索引详解 #### 一、Oracle分区概述 在Oracle数据库中,分区技术是一种非常有效的管理大型表和索引的方法。通过将一个大的表或索引分成多个较小的部分(分区),可以显著提高查询性能,并...
### Oracle表分区详解 #### 一、概述 Oracle数据库中的表分区是一种高级组织技术,它通过将表的大数据集划分为较小的、更易于管理的部分(即分区),从而提高查询性能和可管理性。表分区可以按照不同的策略进行...
### Oracle 分区表详解 #### 一、Oracle 分区简介 Oracle 的分区技术是一种用于管理和优化超大型表和索引的有效手段。通过将一个大型的表或者索引分割成多个较小且可管理的部分,分区技术能够显著提升数据库的性能...
- `INDEXPARTITION`: 分区索引。 - `TABLEPARTITION`: 分区表。 - `NESTEDTABLE`: 嵌套表。 - `ROLLBACK`: 回滚段。 - `LOBPARTITION`: LOB分区。 - `LOBSEGMENT`: LOB段。 - `INDEX`: 普通索引。 - `TABLE`: 表。 -...
ADD PARTITION` 命令来增加新的分区。 - **修改分区**:使用 `ALTER TABLE ... MODIFY PARTITION` 来更改现有分区的属性。 - **删除分区**:使用 `ALTER TABLE ... DROP PARTITION` 删除不需要的分区。 - **合并...
### Oracle表自动按月分区步骤详解 #### 一、背景介绍 在大数据处理与分析领域,数据库表的性能优化显得尤为重要。特别是在面对大量历史数据时,合理地利用表分区技术可以显著提高查询效率,减少资源消耗。Oracle...
支持自动ORACLE大表分区: 版本进度: 31. 20110420 V2.2 支持任意表任意时间字段分区 以下为安装部署部分: 1.分区相关脚本部署执行顺序,安装前请确保该用户拥有管理员权限, 同时请执行GRANT CREATE ANY TABLE ...