当表中的数据量不断增大,查询数据的速度就会变慢,应用程序的性能就会下降,这时就应该考虑对表进行分区。表进行分区后,逻辑上表仍然是一张完整的表,只是将表中的数据在物理上存放到多个表空间(物理文件上),这样查询数据时,不至于每次都扫描整张表。
表分区的优点
改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度
增强可用性:如果表的某个分区出现故障,表在其他分区的数据仍然可用
维护方便:如果表的某个分区出现故障,需要修复数据,只修复该分区即可
均衡I/O:可以把不同的分区映射到磁盘以平衡I/O,改善整个系统性能
缺点:
分区表相关:已经存在的表没有方法可以直接转化为分区表。不过Oracle提供了在线重定义表的功能
使用时机
表的大小超过2GB
表中包含历史数据,新的数据被增加都新的分区中
表分区的类型
范围分区
CREATE TABLE ORDERS ( ORDER_ID NUMBER(7) NOT NULL, ORDER_DATE DATE, TOTAL_AMOUNT NUMBER, CUSTOTMER_ID NUMBER(7), PAID CHAR(1) ) PARTITION BY RANGE (ORDER_DATE) ( PARTITION PART01 VALUES LESS THAN (TO_DATE('01-MAY-2003','DD-MON-YYYY')) TABLESPACE ORD_TS01, PARTITION PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) TABLESPACE ORD_TS02, PARTITION PART02 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY')) TABLESPACE ORD_TS03 ); #MAXVALUE的使用 PARTITION BY RANGE (grade) ( PARTITION part1 VALUES LESS THEN (1000) TABLESPACE Part1_tb, PARTITION part2 VALUES LESS THEN (MAXVALUE) TABLESPACE Part2_tb );
列表分区
PARTITION BY LIST (area) ( PARTITION part1 VALUES ('guangdong','beijing') TABLESPACE Part1_tb, PARTITION part2 VALUES ('shanghai','nanjing') TABLESPACE Part2_tb );
散列分区
根据hash算法来计算具体某条纪录应该插入到哪个分区中。建议分区的数量采用2的n次方,这样可以使得各个分区间数据分布更加均匀。
PARTITION BY HASH (COL) ( PARTITION PART01 TABLESPACE HASH_TS01, PARTITION PART02 TABLESPACE HASH_TS02, PARTITION PART03 TABLESPACE HASH_TS03 ); PARTITION BY HASH (empno) PARTITIONS 8 STORE IN (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);
组合范围散列分区
这种分区是基于范围分区和列表分区,表首先按某列进行范围分区,然后再按某列进行列表分区,分区之中的分区被称为子分区。
PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS) ( PARTITION P1 VALUES LESS THAN(TO_DATE('2003-01-01','YYYY-MM-DD'))TABLESPACE rptfact2009 ( SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009, SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009 ), PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE rptfact2009 ( SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009, SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009 ) )
复合范围散列分区
这种分区是基于范围分区和散列分区,表首先按某列进行范围分区,然后再按某列进行散列分区。
partition by range(transaction_date)subpartition by hash(transaction_id) subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03) ( partition part_01 values less than(to_date(‘2006-01-01’,’yyyy-mm-dd’)), partition part_02 values less than(to_date(‘2010-01-01’,’yyyy-mm-dd’)), partition part_03 values less than(maxvalue) );
表分区的维护
添加分区
ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));
ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');
删除分区
ALTER TABLE SALES DROP PARTITION P3;
ALTER TABLE SALES DROP SUBPARTITION P4SUB1;
截断分区
指删除某个分区中的数据,并不会删除分区,也不会删除其它分区中的数据。
ALTER TABLE SALES TRUNCATE PARTITION P2;
ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;
合并分区
将相邻的分区合并成一个分区,结果分区将采用较高分区的界限。
ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;
拆分分区
将一个分区拆分两个新分区,拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。
ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);
接合分区(coalesca)
将散列分区中的数据接合到其它分区中,当散列分区中的数据比较大时,可以增加散列分区,然后进行接合,值得注意的是,接合分区只能用于散列分区中。
ALTER TABLE SALES COALESCA PARTITION;
重命名表分区
ALTER TABLE SALES RENAME PARTITION P21 TO P2;
查询表上有多少分区
SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName'
查询出所有的分区表
select * from user_tables a where a.partitioned='YES'
显示数据库所有分区表的信息
select * from DBA_PART_TABLES
评论