关于Oracle表分区.docx

1、关于Oracle表分区关于Oracle表分区关于Oracle表分区（转）在ORACLE里如果遇到特别大的表，可以使用分区的表来改变其应用程序的性能。同事的分区表总结，转载一下。1.1 分区表PARTITION table在ORACLE里如果遇到特别大的表，可以使用分区的表来改变其应用程序的性能。1.1.1 分区表的建立：某公司的每年产生巨大的销售记录，DBA向公司建议每季度的数据放在一个分区内，以下示范的是该公司1999年的数据(假设每月产生30M的数据)，操作如下：范围分区表：CREATE TABLE sales(invoice_no NUMBER,.sale_date DATE NOT N

2、ULL )PARTITION BY RANGE (sale_date)(PARTITION sales1999_q1VALUES LESS THAN (TO_DATE(1999-04-01,YYYY-MM-DD)TABLESPACE ts_sale1999q1,PARTITION sales1999_q2VALUES LESS THAN (TO_DATE(1999-07-01,YYYY-MM-DD)TABLESPACE ts_sale1999q2,PARTITION sales1999_q3VALUES LESS THAN (TO_DATE(1999-10-01,YYYY-MM-DD)TABL

3、ESPACE ts_sale1999q3,PARTITION sales1999_q4VALUES LESS THAN (TO_DATE(2000-01-01,YYYY-MM-DD)TABLESPACE ts_sale1999q4 );-values less than (maxvalue)列表分区表：create table emp (empno number(4),ename varchar2(30),location varchar2(30)partition by list (location)(partition p1 values (北京),partition p2 values

4、(上海,天津,重庆),partition p3 values (广东,福建)partition p0 values (default);哈希分区：create table emp (empno number(4),ename varchar2(30),sal number)partition by hash (empno)partitions 8store in (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);组合分区：范围哈希组合分区：create table emp (empno number(4),ename varchar2(30),hiredate

5、 date)partition by range (hiredate)subpartition by hash (empno)subpartitions 2(partition e1 values less than (to_date(20020501,YYYYMMDD),partition e2 values less than (to_date(20021001,YYYYMMDD),partition e3 values less than (maxvalue);范围列表组合分区：CREATE TABLE customers_part (customer_id NUMBER(6),cust

6、_first_name VARCHAR2(20),cust_last_name VARCHAR2(20),nls_territory VARCHAR2(30),credit_limit NUMBER(9,2)PARTITION BY RANGE (credit_limit)SUBPARTITION BY LIST (nls_territory)SUBPARTITION TEMPLATE(SUBPARTITION east VALUES (CHINA, JAPAN, INDIA, THAILAND),SUBPARTITION west VALUES (AMERICA, GERMANY, ITAL

7、Y, SWITZERLAND),SUBPARTITION other VALUES (DEFAULT)(PARTITION p1 VALUES LESS THAN (1000),PARTITION p2 VALUES LESS THAN (2500),PARTITION p3 VALUES LESS THAN (MAXVALUE);create table t1 (id1 number,id2 number)partition by range (id1) subpartition by list (id2)(partition p11 values less than (11)(subpar

8、tition subp1 values (1);索引分区：CREATE INDEX month_ix ON sales(sales_month)GLOBAL PARTITION BY RANGE(sales_month)(PARTITION pm1_ix VALUES LESS THAN (2)PARTITION pm12_ix VALUES LESS THAN (MAXVALUE);1.1.2 分区表的维护：增加分区：ALTER TABLE sales ADD PARTITION sales2000_q1VALUES LESS THAN (TO_DATE(2000-04-01,YYYY-MM

9、-DD)TABLESPACE ts_sale2000q1;如果已有maxvalue分区，不能增加分区，可以采取分裂分区的办法增加分区！删除分区：ALTER TABLE sales DROP PARTION sales1999_q1;截短分区:alter table sales truncate partiton sales1999_q2;合并分区：alter table sales merge partitons sales1999_q2, sales1999_q3 into sales1999_q23;alter index ind_t2 rebuild partition p123 par

10、allel 2;分裂分区：ALTER TABLE salesSPLIT PARTITON sales1999_q4AT TO_DATE (1999-11-01,YYYY-MM-DD)INTO (partition sales1999_q4_p1, partition sales1999_q4_p2) ;alter table t2 split partition p123 values (1,2) into (partition p12,partition p3);交换分区:alter table x exchange partition p0 with table bsvcbusrundat

11、ald ;访问指定分区：select * from sales partition(sales1999_q2)EXPORT指定分区：exp sales/sales_password tables=sales:sales1999_q1file=sales1999_q1.dmpIMPORT指定分区：imp sales/sales_password FILE =sales1999_q1.dmpTABLES = (sales:sales1999_q1) IGNORE=y查看分区信息：user_tab_partitions, user_segments注：若分区表跨不同表空间，做导出、导入时目标数据库必

12、须预建这些表空间。分表区各区所在表空间在做导入时目标数据库一定要预建这些表空间！这些表空间不一定是用户的默认表空间，只要存在即可。如果有一个不存在，就会报错！默认时，对分区表的许多表维护操作会使全局索引不可用，标记成UNUSABLE。 那么就必须重建整个全局索引或其全部分区。如果已被分区，Oracle 允许在用于维护操作的ALTER TABLE 语句中指定UPDATE GLOBAL INDEXES 来重载这个默认特性，指定这个子句也就告诉Oracle 当它执行维护操作的DDL 语句时更新全局索引，这提供了如下好处：1.在操作基础表的同时更新全局索引这就不需要后来单独地重建全局索引；2.因为没有

13、被标记成UNUSABLE， 所以全局索引的可用性更高了，甚至正在执行分区的DDL 语句时仍然可用索引来访问表中的其他分区,避免了查询所有失效的全局索引的名字以便重建它们；另外在指定UPDATE GLOBAL INDEXES 之前还要考虑如下性能因素:1.因为要更新事先被标记成UNUSABLE 的索引，所以分区的DDL 语句要执行更长时间，当然这要与先不更新索引而执行DDL 然后再重建索引所花的时间做个比较，一个适用的规则是如果分区的大小小于表的大小的5% ，则更新索引更快一点；2.DROP TRUNCATE 和EXCHANGE 操作也不那么快了，同样这必须与先执行DDL 然后再重建所有全局索引

14、所花的时间做个比较；3.要登记对索引的更新并产生重做记录和撤消记录，重建整个索引时可选择NOLOGGING；4.重建整个索引产生一个更有效的索引，因为这更利于使用空间，再者重建索引时允许修改存储选项。注意分区索引结构表不支持UPDATE GLOBAL INDEXES 子句。1.1.3 普通表变为分区表将已存在数据的普通表转变为分区表，没有办法通过修改属性的方式直接转化为分区表，必须通过重建的方式进行转变，一般可以有三种方法，视不同场景使用：用例：方法一：利用原表重建分区表。CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);INSERT INTO

15、 TSELECT ROWNUM, SYSDATE - ROWNUM FROM DBA_OBJECTS WHERE ROWNUM = 5000;COMMIT;CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)(PARTITION P1 VALUES LESS THAN (TO_DATE(2000-1-1, YYYY-MM-DD),PARTITION P2 VALUES LESS THAN (TO_DATE(2002-1-1, YYYY-MM-DD),PARTITION P3 VALUES LESS THAN (TO_DATE(2005-

16、1-1, YYYY-MM-DD),PARTITION P4 VALUES LESS THAN (MAXVALUE)AS SELECT ID, TIME FROM T;RENAME T TO T_OLD;RENAME T_NEW TO T;SELECT COUNT(*) FROM T;COUNT(*)-5000SELECT COUNT(*) FROM T PARTITION (P1);COUNT(*)-2946SELECT COUNT(*) FROM T PARTITION (P2);COUNT(*)-731SELECT COUNT(*) FROM T PARTITION (P3);COUNT(

17、*)-1096优点：方法简单易用，由于采用DDL语句，不会产生UNDO，且只产生少量REDO，效率相对较高，而且建表完成后数据已经在分布到各个分区中了。不足：对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性，在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失，如果要保证一致性，需要在执行完语句后对数据进行检查，而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。适用于修改不频繁的表，在闲时进行操作，表的数据量不宜太大。方法二：使用交换分区的方法。Drop table t;CREATE

18、TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);INSERT INTO TSELECT ROWNUM, SYSDATE - ROWNUM FROM DBA_OBJECTS WHERE ROWNUM = 5000;COMMIT;CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)(PARTITION P1 VALUES LESS THAN (TO_DATE(2005-9-1, YYYY-MM-DD),PARTITION P2 VALUES LESS TH

19、AN (MAXVALUE);ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T;RENAME T TO T_OLD;RENAME T_NEW TO T;优点：只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。如果对数据在分区中的分布没有进一步要求的话，实现比较简单。在执行完RENAME操作后，可以检查T_OLD中是否存在数据，如果存在的话，直接将这些数据插入到T中，可以保证对T插入的操作不会丢失。不足：仍然存在一致性问题，交换分区之后RENAME T_NEW TO T之前，查询、更新和删除会出现错误或访问不到数据。如

20、果要求数据分布到多个分区中，则需要进行分区的SPLIT操作，会增加操作的复杂度，效率也会降低。适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。方法三：Oracle9i以上版本，利用在线重定义功能Drop table t;CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);INSERT INTO TSELECT ROWNUM, SYSDATE - ROWNUM FROM DBA_OBJECTS WHERE ROWNUM sysdate-2create table x2 nologging as select * from t1 where recdatetrunc(sysdate-2)alter triger trg_t1 disabledelete t1 where recdatesysdate-2commitrename t1 to x1create table t1 nologging partitio