Oracle 分区表总结.docx

Oracle 分区表总结.docx

《Oracle 分区表总结.docx》由会员分享，可在线阅读，更多相关《Oracle 分区表总结.docx（13页珍藏版）》请在冰豆网上搜索。

Oracle分区表总结

Oracle分区表总结

在ORACLE里如果遇到特别大的表，可以使用分区的表来改变其应用程序的性能。

同事的分区表总结，转载一下。

1.1分区表PARTITIONtable

在ORACLE里如果遇到特别大的表，可以使用分区的表来改变其应用程序的性能。

1.1.1分区表的建立：

某公司的每年产生巨大的销售记录，DBA向公司建议每季度的数据放在一个分区内，以下示范的是该公司1999年的数据（假设每月产生30M的数据），操作如下：

范围（rangepartitionedtable）分区表：

CREATETABLEsales

（invoice_noNUMBER,

...

sale_dateDATENOTNULL）

PARTITIONBYRANGE（sale_date）

（PARTITIONsales1999_q1

VALUESLESSTHAN（TO_DATE（‘1999-04-01’,’YYYY-MM-DD’）

TABLESPACEts_sale1999q1,

PARTITIONsales1999_q2

VALUESLESSTHAN（TO_DATE（‘1999-07-01’,’YYYY-MM-DD’）

TABLESPACEts_sale1999q2,

PARTITIONsales1999_q3

VALUESLESSTHAN（TO_DATE（‘1999-10-01’,’YYYY-MM-DD’）

TABLESPACEts_sale1999q3,

PARTITIONsales1999_q4

VALUESLESSTHAN（TO_DATE（‘2000-01-01’,’YYYY-MM-DD’）

TABLESPACEts_sale1999q4）;

--valueslessthan（maxvalue）/*余下的未选择日期都归于该分区

列表（listpartitioning）分区表：

createtableemp（

empnonumber（4）,

enamevarchar2（30）,

locationvarchar2（30））

partitionbylist（location）

（partitionp1values（'北京'）,

partitionp2values（'上海','天津','重庆'）,

partitionp3values（'广东','福建'）

partitionp0values（default）/*值列表中未显示列出的所有值都会放到这个default分区中，关于default的使用，有一点要注意：

一旦列表分区表有一个default分区，就不能再向这个表中增加更多的分区了，此时必须删除default分区，然后增加新分区，再加回default分区。

）;

哈希（hashpartitioning）分区：

散列分区数应该是2的幂，从而利于各行平均的散列与各分区

createtableemp（

empnonumber（4）,

enamevarchar2（30）,

salnumber）

partitionbyhash（empno）

partitions8

storein（emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8）;

组合分区：

范围哈希组合分区：

createtableemp（

empnonumber（4）,

enamevarchar2（30）,

hiredatedate）

partitionbyrange（hiredate）

subpartitionbyhash（empno）

subpartitions2

（partitione1valueslessthan（to_date（'20020501','YYYYMMDD'））,

partitione2valueslessthan（to_date（'20021001','YYYYMMDD'））,

partitione3valueslessthan（maxvalue））;

范围列表组合分区（compositepartitioning）：

在组合分区中，顶层分区机制总是区间分区，第二级分区机制可能是列表分区或者散列分区，使用组合分区时，并没有分区段，只有子分区段，分区本身并没有段（这就类似于分区表没有段），数据物理的存储在子分区段上，分区成为一个逻辑容器，或者指向实际子分区的容器。

每个区间分区不需要有相同数目的子分区。

CREATETABLEcustomers_part（

customer_idNUMBER（6）,

cust_first_nameVARCHAR2（20）,

cust_last_nameVARCHAR2（20）,

nls_territoryVARCHAR2（30）,

credit_limitNUMBER（9,2））

PARTITIONBYRANGE（credit_limit）

SUBPARTITIONBYLIST（nls_territory）

SUBPARTITIONTEMPLATE

（SUBPARTITIONeastVALUES（'CHINA','JAPAN','INDIA','THAILAND'）,

SUBPARTITIONwestVALUES（'AMERICA','GERMANY','ITALY','SWITZERLAND'）,

SUBPARTITIONotherVALUES（DEFAULT））

（PARTITIONp1VALUESLESSTHAN（1000）,

PARTITIONp2VALUESLESSTHAN（2500）,

PARTITIONp3VALUESLESSTHAN（MAXVALUE））;

索引分区：

CREATEINDEXmonth_ixONsales（sales_month）

GLOBALPARTITIONBYRANGE（sales_month）

（PARTITIONpm1_ixVALUESLESSTHAN

（2）

PARTITIONpm12_ixVALUESLESSTHAN（MAXVALUE））;

1.1.2分区表的维护：

增加分区：

ALTERTABLEsalesADDPARTITIONsales2000_q1

VALUESLESSTHAN（TO_DATE（‘2000-04-01’,’YYYY-MM-DD’）

TABLESPACEts_sale2000q1;

如果已有maxvalue分区，不能增加分区，可以采取分裂分区的办法增加分区！

删除分区：

ALTERTABLEsalesDROPPARTIONsales1999_q1;

截短分区:

altertablesalestruncatepartitonsales1999_q2;

合并分区：

altertablesalesmergepartitonssales1999_q2,sales1999_q3intosales1999_q23;

alterindexind_t2rebuildpartitionp123parallel2;

分裂分区：

ALTERTABLEsales

SPLITPARTITONsales1999_q4

ATTO_DATE（‘1999-11-01’,’YYYY-MM-DD’）

INTO（partitionsales1999_q4_p1,partitionsales1999_q4_p2）;

altertablet2splitpartitionp123values（1,2）into（partitionp12,partitionp3）;

交换分区:

altertablexexchangepartitionp0withtablebsvcbusrundatald;

访问指定分区：

select*fromsalespartition（sales1999_q2）

EXPORT指定分区：

expsales/sales_passwordtables=sales:

sales1999_q1

file=sales1999_q1.dmp

IMPORT指定分区：

impsales/sales_passwordFILE=sales1999_q1.dmp

TABLES=（sales:

sales1999_q1）IGNORE=y

查看分区信息：

user_tab_partitions,user_segments

注：

若分区表跨不同表空间，做导出、导入时目标数据库必须预建这些表空间。

分表区各区所在表空间在做导入时目标数据库一定要预建这些表空间！

这些表空间不一定是用户的默认表空间，只要存在即可。

如果有一个不存在，就会报错！

默认时，对分区表的许多表维护操作会使全局索引不可用，标记成UNUSABLE。

那么就必须重建整个全局索引或其全部分区。

如果已被分区，Oracle允许在用于维护操作的ALTERTABLE语句中指定UPDATEGLOBALINDEXES来重载这个默认特性，指定这个子句也就告诉Oracle当它执行维护操作的DDL语句时更新全局索引，这提供了如下好处：

1.在操作基础表的同时更新全局索引这就不需要后来单独地重建全局索引；

2.因为没有被标记成UNUSABLE，所以全局索引的可用性更高了，甚至正在执行分区的DDL语句时仍然可用索引来访问表中的其他分区,避免了查询所有失效的全局索引的名字以便重建它们；

另外在指定UPDATEGLOBALINDEXES之前还要考虑如下性能因素:

1.因为要更新事先被标记成UNUSABLE的索引，所以分区的DDL语句要执行更长时间，当然这要与先不更新索引而执行DDL然后再重建索引所花的时间做个比较，一个适用的规则是如果分区的大小小于表的大小的5%，则更新索引更快一点；

2.DROPTRUNCATE和EXCHANGE操作也不那么快了，同样这必须与先执行DDL然后再重建所有全局索引所花的时间做个比较；

3.要登记对索引的更新并产生重做记录和撤消记录，重建整个索引时可选择NOLOGGING；

4.重建整个索引产生一个更有效的索引，因为这更利于使用空间，再者重建索引时允许修改存储选项。

注意分区索引结构表不支持UPDATEGLOBALINDEXES子句。

1.1.3普通表变为分区表

将已存在数据的普通表转变为分区表，没有办法通过修改属性的方式直接转化为分区表，必须通过重建的方式进行转变，一般可以有三种方法，视不同场景使用：

用例：

方法一：

利用原表重建分区表。

CREATETABLET（IDNUMBERPRIMARYKEY,TIMEDATE）;

INSERTINTOT

SELECTROWNUM,SYSDATE-ROWNUMFROMDBA_OBJECTSWHEREROWNUM<=5000;

COMMIT;

CREATETABLET_NEW（ID,TIME）PARTITIONBYRANGE（TIME）

（PARTITIONP1VALUESLESSTHAN（TO_DATE（'2000-1-1','YYYY-MM-DD'））,

PARTITIONP2VALUESLESSTHAN（TO_DATE（'2002-1-1','YYYY-MM-DD'））,

PARTITIONP3VALUESLESSTHAN（TO_DATE（'2005-1-1','YYYY-MM-DD'））,

PARTITIONP4VALUESLESSTHAN（MAXVALUE））

ASSELECTID,TIMEFROMT;

RENAMETTOT_OLD;

RENAMET_NEWTOT;

SELECTCOUNT（*）FROMT;

COUNT（*）

----------

5000

SELECTCOUNT（*）FROMTPARTITION（P1）;

COUNT（*）

----------

2946

SELECTCOUNT（*）FROMTPARTITION（P2）;

COUNT（*）

----------

731

SELECTCOUNT（*）FROMTPARTITION（P3）;

COUNT（*）

----------

1096

优点：

方法简单易用，由于采用DDL语句，不会产生UNDO，且只产生少量REDO，效率相对较高，而且建表完成后数据已经在分布到各个分区中了。

不足：

对于数据的一致性方面还需要额外的考虑。

由于几乎没有办法通过手工锁定T表的方式保证一致性，在执行CREATETABLE语句和RENAMET_NEWTOT语句直接的修改可能会丢失，如果要保证一致性，需要在执行完语句后对数据进行检查，而这个代价是比较大的。

另外在执行两个RENAME语句之间执行的对T的访问会失败。

适用于修改不频繁的表，在闲时进行操作，表的数据量不宜太大。

方法二：

使用交换分区的方法。

Droptablet;

CREATETABLET（IDNUMBERPRIMARYKEY,TIMEDATE）;

INSERTINTOT

SELECTROWNUM,SYSDATE-ROWNUMFROMDBA_OBJECTSWHEREROWNUM<=5000;

COMMIT;

CREATETABLET_NEW（IDNUMBERPRIMARYKEY,TIMEDATE）PARTITIONBYRANGE（TIME）

（PARTITIONP1VALUESLESSTHAN（TO_DATE（'2005-9-1','YYYY-MM-DD'））,

PARTITIONP2VALUESLESSTHAN（MAXVALUE））;

ALTERTABLET_NEWEXCHANGEPARTITIONP1WITHTABLET;

RENAMETTOT_OLD;

RENAMET_NEWTOT;

优点：

只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。

如果对数据在分区中的分布没有进一步要求的话，实现比较简单。

在执行完RENAME操作后，可以检查T_OLD中是否存在数据，如果存在的话，直接将这些数据插入到T中，可以保证对T插入的操作不会丢失。

不足：

仍然存在一致性问题，交换分区之后RENAMET_NEWTOT之前，查询、更新和删除会出现错误或访问不到数据。

如果要求数据分布到多个分区中，则需要进行分区的SPLIT操作，会增加操作的复杂度，效率也会降低。

适用于包含大数据量的表转到分区表中的一个分区的操作。

应尽量在闲时进行操作。

方法三：

Oracle9i以上版本，利用在线重定义功能

Droptablet;

CREATETABLET（IDNUMBERPRIMARYKEY,TIMEDATE）;

INSERTINTOT

SELECTROWNUM,SYSDATE-ROWNUMFROMDBA_OBJECTSWHEREROWNUM<=5000;

COMMIT;

EXECDBMS_REDEFINITION.CAN_REDEF_TABLE（USER,'T'）;

PL/SQL过程已成功完成。

CREATETABLET_NEW（IDNUMBERPRIMARYKEY,TIMEDATE）PARTITIONBYRANGE（TIME）

（PARTITIONP1VALUESLESSTHAN（TO_DATE（'2004-7-1','YYYY-MM-DD'））,

PARTITIONP2VALUESLESSTHAN（TO_DATE（'2005-1-1','YYYY-MM-DD'））,

PARTITIONP3VALUESLESSTHAN（TO_DATE（'2005-7-1','YYYY-MM-DD'））,

PARTITIONP4VALUESLESSTHAN（MAXVALUE））;

表已创建。

EXECDBMS_REDEFINITION.START_REDEF_TABLE（USER,'T','T_NEW'）;

PL/SQL过程已成功完成。

EXECDBMS_REDEFINITION.FINISH_REDEF_TABLE（USER,'T','T_NEW'）;

PL/SQL过程已成功完成。

SELECTCOUNT（*）FROMT;

COUNT（*）

----------

5000

SELECTCOUNT（*）FROMTPARTITION（P3）;

COUNT（*）

----------

1096

优点：

保证数据的一致性，在大部分时间内，表T都可以正常进行DML操作。

只在切换的瞬间锁表，具有很高的可用性。

这种方法具有很强的灵活性，对各种不同的需要都能满足。

而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。

不足：

实现上比上面两种略显复杂。

适用于各种情况。

这里只给出了在线重定义表的一个最简单的例子，详细的描述和例子可以参考下面两篇文章。

Oracle的在线重定义表功能：

Oracle的在线重定义表功能

（二）：

XSB:

把一个已存在数据的大表改成分区表：

第一种（表不是太大）：

1.把原表改名：

renamexsb1toxsb2;

2.创建分区表：

CREATETABLExsb1

PARTITIONBYLIST（c_test）

（PARTITIONxsb1_p1VALUES

（1）,

PARTITIONxsb1_p2VALUES

（2）,

PARTITIONxsb1_p0VALUES（default））

nologgingASSELECT*FROMxsb2;

3.将原表上的触发器、主键、索引等应用到分区表上；

4.删除原表：

droptablexsb2;

第二种（表很大）：

1.创建分区表：

CREATETABLExPARTITIONBYLIST（c_test）[range（）]

（PARTITIONp0VALUES[lessthan]

（1）tablespacetbs1,

PARTITIONp2VALUES

（2）tablespacetbs1,

PARTITIONxsb1_p0VALUES（[maxvalue]default））

ASSELECT*FROMxsb2[where1=2];

2.交换分区altertablexexchangepartitionp0withtablebsvcbusrundatald;

3.原表改名altertablebsvcbusrundataldrenametox0;

4.新表改名altertablexrenametobsvcbusrundatald;

5.删除原表droptablex0;

6.创建新表触发器和索引createindexind_busrundata_lponbsvcbusrundatald（。

。

。

）localtablespacetbs_brd_ind;

或者：

1.规划原大表中数据分区的界限，原则上将原表中近期少量数据复制至另一表；

2.暂停原大表中的相关触发器；

3.删除原大表中近期数据；

4.改名原大表名称；

5.创建分区表；

6.交换分区；

7.重建相关索引及触发器（先删除之再重建）.

参考脚本：

selectcount（*）fromt1whererecdate>sysdate-2

createtablex2nologgingasselect*fromt1whererecdate>trunc（sysdate-2）

altertrigertrg_t1disable

deletet1whererecdate>sysdate-2

commit

renamet1tox1

createtablet1[nologging]partitionbyrange（recdate）

（partitionpbeforevalueslessthan（trunc（sysdate-2））,

partitionpmaxvalueslessthan（maxvalue））

asselect*fromx1where1=2

altertablet1exchangepartitionpbeforewithtablex1

altertablet1exchangepartitionpmaxwithtablex2

droptablex2

[重建触发器]

droptablex1

1.1.4参考材料：

如果表中预期的数据量较大，通常都需要考虑使用分区表，确定使用分区表后，还要确定什么类型的分区（rangepartition、hashpartition、listpartition等）、分区区间大小等。

分区的创建最好与程序有某种默契，偶曾经创建分区表，按自然月份定义分区的，但程序却在查询时默认的开始时间与结束时间是：

当前日期-30至当前日期，比如当天是9.18号，那查询条件被产生为8.18-9.18，结果分区后并不没有大幅提高性能，后来对程序的查询日期做了调整，按自然月查询，系统的负载小了很多。

从Oracle8.0开始支持表分区（MSSQL2005开始支持表分区）。

Oracle9i分区能够提高许多应用程序的可管理性、性能与可用性。

分区可以将表、索引及索引编排表进一步划分，从而可以更精细地对这些数据库对象进行管理和访问。

Oracle提供了种类繁多的分区方案以满足所有的业务需要。

另外，由于在SQL语句中是完全透明的，所以分区可以用于几乎所有的应用程序。

分区表允许将数据分成被称为分区甚至子分区的更小的更好管理的块。

索引也可以这么分区。

每个分区可以被单独管理，可以不依赖于其他分区而单独发挥作用，因此提供了一个更有利于可用性和性能的结构。

分区可以提高可管理性、性能与可用性，从而给各种各样的应用程序带来极大的好处。

通常，分区可以使某些查询以及维护操作的性能大大提高。

此外，分区还能够在很大程度上简化日常管理任务。

分区还使数据库设计人员和管理员能够解决尖端应用程序带来的最难的问题。

分区是建立上亿万字节数据系统或需要极高可用性系统的关键工具。

在多CPU配置环境下，如果打算使用并行执行，则分区提供了另