数据库表分区方法(ORACLE)

oracle数据库表分区 摘要:在大量业务数据处理的项目中,可以考虑使用分区表来提高应用系统的性能并方便数据管理,本文详细介绍了分区表的使用。 在大型的企业应用或企业级的数据库应用中,要处理的数据量通常可以达到几十到几百GB,有的甚至可以到TB级。虽然存储介质和数据处理技术的发展也很快,但是仍然不能满足用户的需求,为了使用户的大量的数据在读写操作和查询中速度更快,Oracle提供了对表和索引进行分区的技术,以改善大型应用系统的性能。 使用分区的优点: ·增强可用性:如果表的某个分区出现故障,表在其他分区的数据仍然可用; ·维护方便:如果表的某个分区出现故障,需要修复数据,只修复该分区即可; ·均衡I/O:可以把不同的分区映射到磁盘以平衡I/O,改善整个系统性能; ·改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度。 Oracle数据库提供对表或索引的分区 方法 快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载 有三种: ·范围分区 ·Hash分区(散列分区) ·复合分区 下面将以实例的方式分别对这三种分区方法来说明分区表的使用。为了测试方便,我们先建三个表空间。 createtablespace dinya_space01 datafile ’/test/demo/oracle/demodata/dinya01.dnf’ size 50M createtablespace dinya_space01 datafile ’/test/demo/oracle/demodata/dinya02.dnf’ size 50M createtablespace dinya_space01 datafile ’/test/demo/oracle/demodata/dinya03.dnf’ size 50M 1.1. 分区表的创建 1.1.1. 范围分区 范围分区就是对数据表中的某个值的范围进行分区,根据某个值的范围,决定将该数据存储在哪个分区上。如根据序号分区,根据业务记录的创建日期进行分区等。 需求描述:有一个物料交易表,表名:material_transactions。该表将来可能有千万级的数据记录数。要求在建该表的时候使用分区表。这时候我们可以使用序号分区三个区,每个区中预计存储三千万的数据,也可以使用日期分区,如每五年的数据存储在一个分区上。 根据交易记录的序号分区建表: SQL> create table dinya_test 2 ( 3 transaction_id number primary key, 4 item_idnumber(8) not null, 5 item_descriptionvarchar2(300), 6 transaction_date date not null 7 ) 8 partition by range (transaction_id) 9 ( 10 partition part_01 values less than(30000000) tablespace dinya_space01, 11 partition part_02 values less than(60000000) tablespace dinya_space02, 12 partition part_03 values less than(maxvalue) tablespace dinya_space03 13 ); Table created. 建表成功,根据交易的序号,交易ID在三千万以下的记录将存储在第一个表空间dinya_space01中,分区名为:par_01,在三千万到六千万之间的记录存储在第二个表空间: dinya_space02中,分区名为:par_02,而交易ID在六千万以上的记录存储在第三个表空间dinya_space03中,分区名为par_03. 根据交易日期分区建表: SQL> create table dinya_test 2 ( 3 transaction_id number primary key, 4 item_idnumber(8) not null, 5 item_descriptionvarchar2(300), 6 transaction_date date not null 7 ) 8 partition by range (transaction_date) 9 ( 10 partition part_01 values less than(to_date(’2006-01-01’,’yyyy-mm-dd’)) tablespace dinya_space01, 11 partitio n part_02 values less than(to_date(’2010-01-01’,’yyyy-mm-dd’)) tablespace dinya_space02, 12 partition part_03 values less than(maxvalue) tablespace dinya_space03 13 ); Table created. 这样我们就分别建了以交易序号和交易日期来分区的分区表。每次插入数据的时候,系统将根据指定的字段的值来自动将记录存储到制定的分区(表空间)中。 当然,我们还可以根据需求,使用两个字段的范围分布来分区,如partition by range ( transaction_id ,transaction_date), 分区条件中的值也做相应的改变,请读者自行测试。 1.1. 2. Hash分区(散列分区) 散列分区为通过指定分区编号来均匀分布数据的一种分区类型,因为通过在I/O设备上进行散列分区,使得这些分区大小一致。如将物料交易表的数据根据交易ID散列地存放在指定的三个表空间中: SQL> create table dinya_test 2 ( 3 transaction_id number primary key, 4 item_idnumber(8) not null, 5 item_descriptionvarchar2(300), 6 transaction_date date 7 ) 8 partition by hash(transaction_id) 9 ( 10 partition part_01 tablespace dinya_space01, 11 partition part_02 tablespace dinya_space02, 12 partition part_03 tablespace dinya_space03 13 ); Table created. 建表成功,此时插入数据,系统将按transaction_id将记录散列地插入三个分区中,这里也就是三个不同的表空间中。 1.1.3. 复合分区 有时候我们需要根据范围分区后,每个分区内的数据再散列地分布在几个表空间中,这样我们就要使用复合分区。复合分区是先使用范围分区,然后在每个分区内再使用散列分区的一种分区方法,如将物料交易的记录按时间分区,然后每个分区中的数据分三个子分区,将数据散列地存储在三个指定的表空间中: SQL> create table dinya_test 2 ( 3 transaction_id number primary key, 4 item_idnumber(8) not null, 5 item_descriptionvarchar2(300), 6 transaction_date date 7 ) 8 partition by range(transaction_date)subpartition by hash(transaction_id) 9 subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03) 10 ( 11 partition part_01 values less than(to_date(’2006-01-01’,’yyyy-mm-dd’)), 12 p artition part_02 values less than(to_date(’2010-01-01’,’yyyy-mm-dd’)), 13 partition part_03 values less than(maxvalue) 14 ); Table created. Oracle数据库分区表操作方法 1.1 分区表PARTITION table 在ORACLE里如果遇到特别大的表,可以使用分区的表来改变其应用程序的性能。 1.1.1 分区表的建立: 某公司的每年产生巨大的销售记录,DBA向公司建议每季度的数据放在一个分区内,以下示范的是该公司1999年的数据(假设每月产生30M的数据),操作如下: 范围分区表: CREATE TABLE sales (invoice_no NUMBER, ... sale_date DATE NOT NULL ) PARTITION BY RANGE (sale_date) (PARTITION sales1999_q1 VALUES LESS THAN (TO_DATE(‘1999-04-01’,’YYYY-MM-DD’) TABLESPACE ts_sale1999q1, PARTITION sales1999_q2 VALUES LESS THAN (TO_DATE(‘1999-07-01’,’YYYY-MM-DD’) TABLESPACE ts_sale1999q2, PARTITION sales1999_q3 VALUES LESS THAN (TO_DATE(‘1999-10-01’,’YYYY-MM-DD’) TABLESPACE ts_sale1999q3, PARTITION sales1999_q4 VALUES 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 ('上海','天津','重庆'), partition p3 values ('广东','福建') partition p0 values (default) ); 哈希分区: create table emp ( empno number(4), ename varchar2(30), sal number) partition by hash (empno) partitions 8 store in (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8); 组合分区: 范围哈希组合分区: create table emp ( empno number(4), ename varchar2(30), hiredate 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_first_nameVARCHAR2(20), cust_last_nameVARCHAR2(20), nls_territoryVARCHAR2(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', 'ITALY', '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) (subpartition 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_q1 VALUES LESS THAN (TO_DATE(‘2000-04-01’,’YYYY-MM-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 parallel 2; 分裂分区: ALTER TABLE sales SPLIT PARTITON sales1999_q4 AT 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 bsvcbusrundatald ; 访问指定分区: select * from sales partition(sales1999_q2) EXPORT指定分区: exp sales/sales_password tables=sales:sales1999_q1 file=sales1999_q1.dmp IMPORT指定分区: imp sales/sales_password FILE =sales1999_q1.dmp TABLES = (sales:sales1999_q1) IGNORE=y 查看分区信息: user_tab_partitions, user_segments 注:若分区表跨不同表空间,做导出、导入时目标数据库必须预建这些表空间。分表区各区所在表空间在做导入时目标数据库一定要预建这些表空间!这些表空间不一定是用户的默认表空间,只要存在即可。如果有一个不存在,就会报错! 默认时,对分区表的许多表维护操作会使全局索引不可用,标记成UNUSABLE。那么就必须重建整个全局索引或其全部分区。如果已被分区,Oracle 允许在用于维护操作的ALTER TABLE 语句中指定UPDATE GLOBAL INDEXES 来重载这个默认特性,指定这个子句也就告诉Oracle 当它执行维护操作的DDL 语句时更新全局索引,这提供了如下好处: 1.在操作基础表的同时更新全局索引这就不需要后来单独地重建全局索引; 2.因为没有被标记成UNUSABLE,所以全局索引的可用性更高了,甚至正在执行分区的DDL 语句时仍然可用索引来访问表中的其他分区,避免了查询所有失效的全局索引的名字以便重建它们; 另外在指定UPDATE GLOBAL INDEXES 之前还要考虑如下性能因素: 1.因为要更新事先被标记成UNUSABLE 的索引,所以分区的DDL 语句要执行更长时间,当然这要与先不更新索引而执行DDL 然后再重建索引所花的时间做个比较,一个适用的规则是如果分区的大小小于表的大小的5% ,则更新索引更快一点; 2.DROP TRUNCATE 和EXCHANGE 操作也不那么快了,同样这必须与先执行DDL 然后再重建所有全局索引所花的时间做个比较; 3.要登记对索引的更新并产生重做记录和撤消记录,重建整个索引时可选择NOLOGGING; 4.重建整个索引产生一个更有效的索引,因为这更利于使用空间,再者重建索引时允许修改存储选项。 注意分区索引结构表不支持UPDATE GLOBAL INDEXES 子句。