oracle绝密培训-11.分区表(章节).doc

第十一章：分区表1概述2范围分区2HASH分区（散列分区）3列表分区3复合分区4分区表操作4分区表的管理5第十一章：分区表学习目标n 了解分区表的基本概念n 操作分区表概述在大型的企业应用或企业级的数据库应用中，要处理的数据量通常可以达到几十到几百GB，有的甚至可以到TB级。虽然存储介质和数据处理技术的发展也很快，但是仍然不能满足用户的需求，为了使用户的大量的数据在读写操作和查询中速度更快，Oracle提供了对表和索引进行分区的技术，以改善大型应用系统的性能使用分区表有如下优点：1. 增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；2. 维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；3. 均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能；4. 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度分区的方法有如下几种：1. 范围分区2. HASH分区（散列分区）3. 列表分区4. 复合分区（范围HASH）（范围列表）下面我们创建表空间，以备课程练习使用：CREATE TABLESPACE TEST1 DATAFILE C:TEMPTEST1.DBF SIZE 10MCREATE TABLESPACE TEST2 DATAFILE C:TEMPTEST2.DBF SIZE 10MCREATE TABLESPACE TEST3 DATAFILE C:TEMPTEST3.DBF SIZE 10M范围分区范围分区就是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。如根据序号分区，根据业务记录的创建日期进行分区等。如：物料交易表，表名：material_transactions,将来该表可能有千万条数据记录，要求使用分区表。可以使用序号分区分到3个区上，每个区中预计存储三千万条数据，也可以使用日期分区，如每5年的数据存储在一个分区上。我们下面创建这个表：Create table material_test(transaction_id number primary key,Item_id number(8) not null,Item_description varchar2(300),Transaction_date date not null)Partition by range(transaction_id)(partition part_01 values less than(30000000) tablespace test1,partition part_02 values less than(60000000) tablespace test2,partition part_03 values less than(maxvalue) tablespace test3)当然我们也可以通过时间来进行分区，语法如下：Create table material_test1(transaction_id number primary key,Item_id number(8) not null,Item_description varchar2(300),Transaction_date date not null)Partition by range(transaction_date)(partition part_01 values less than(to_date(2006-01-01,yyyy-mm-dd) tablespace test1,partition part_02 values less than(to_date(2010-01-01,yyyy-mm-dd) tablespace test2,partition part_03 values less than(maxvalue) tablespace test3)HASH分区（散列分区）在列的取值不容易确定时可以采用此方法。如按照身份证进行分区，就很难确定身份证分区范围。HASH实际上是一种算法，当向表插入数据时，系统会自动根据当前分区列的值计算出HASH值之后确定应该将该行存放在哪个表空间。创建散列分区表如下：Create table material_test2(transaction_id number primary key,Item_id number(8) not null,Item_description varchar2(300),Transaction_date date not null)Partition by hash(transaction_id)(partition part_01 tablespace test1,partition part_02 tablespace test2,partition part_03 tablespace test3)列表分区此功能是ORACLE10G增加的功能，对表的某个列的可列举的值进行分区，如果分区的字段的值并不能划分范围，同时分区的值的取值有一个范围，则在分区条件中可以只用枚举的方式列出分区字段的所有选项，从而达到分区的目的。创建列表分区语法如下：Create table material_test3(transaction_id number primary key,Item_id number(8) not null,Item_description varchar2(300),Transaction_date date not null,city varchar2(100)Partition by list(city)(partition part_01 values(北京) tablespace test1,partition part_02 values(上海) tablespace test2,partition part_03 values(default) tablespace test3)复合分区复合分区是一种组合。如：将物料交易的记录按时间分区，然后每个分区中的数据分3个子分区，将数据散列的存储在3个指定的表空间中。创建复合分区的语法如下：Create table material_test4(transaction_id number primary key,Item_id number(8) not null,Item_description varchar2(300),Transaction_date date not null)Partition by range(transaction_date) subpartition by hash(transaction_id)subpartitions 3 store in (test1,test2,test3)(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);分区表操作 下面我们对建立的分区表进行操作，我们以material_test1表为例子，如下：insert into material_test1 values(1,12,BOOKS,sysdate); insert into material_test1 values(2,12, BOOKS,sysdate+30); insert into material_test1 values(3,12, BOOKS,to_date(2006-05-30,yyyy-mm-dd); insert into material_test1 values(4,12, BOOKS,to_date(2007-06-23,yyyy-mm-dd); insert into material_test1 values(5,12, BOOKS,to_date(2011-02-26,yyyy-mm-dd); insert into material_test1 values(6,12, BOOKS,to_date(2011-04-30,yyyy-mm-dd); commit;-查询分区表select * from material_test1 partition(part_01); select * from material_test1 partition(part_02); select * from material_test1 partition(part_03);-分区表与一般的表一样，也支持直接查询select * from material_test1;-更新分区数据update material_test1 partition(part_01) t set t.item_description=DESKwhere t.transaction_id=1; commit; 注意：这里将第一个分区中的交易ID=1的记录中的item_description字段更新为“DESK”，可以看到已经成功更新了一条记录。但是当更新的时候指定了分区，而根据查询的记录不在该分区中时，将不会更新数据。-再次更新分区数据update material_test1 partition(part_01) t set t.item_description=DESK where t.transaction_id=6; commit;注意：指定了在第一个分区中更新记录，但是条件中限制交易ID为6，而查询全表，交易ID为6的记录在第三个分区中，这样该条语句将不会更新记录。-删除分区数据delete from material_test1 partition(part_02) t where t.transaction_id=4; commit; 注意：上面例子删除了第二个分区part_02中的交易记录ID为4的一条记录，和更新数据相同，如果指定了分区，而条件中的数据又不在该分区中时，将不会删除任何数据。n 分区表索引的使用 分区表和一般表一样可以建立索引，分区表可以创建局部索引和全局索引。当分区中出现许多事务并且要保证所有分区中的数据记录的唯一性时采用全局索引。下面我们创建一个局部索引在分区中，如下：create index material_test1 on material_test1(item_id) local ( partition idx_1 tablespace test1, partition idx_2 tablespace test2, partition idx_3 tablespace test3 ); commit;如果没有加选项，与其它建立的表没有区别，语法如下：drop index material_test1;create index material_test1 on material_test1(item_id);分区表的管理分区表的管理如下表所示：维护操作范围HASH复合增加分区ADD PARTITIONADD PARTITIONADD PARTITIONMODIFY PARTITIONADD SUBPARTITION合并分区MERGE PARTITION无MERGE PARTITION删除分区DROP PARTITION无DROP PARTITION互换分区EXCHANGE PARTITIONEXCHANGE PARTITIONEXCHANGE PARTITIONEXCHANGE SUBPARTITION接合分区无COALESCE PARTITIONMODIFY PARTITONCOALESCE SUBPARTITION修改分区默认值MODIFY DEFAULT ATTRIBUTESMODIFY DEFAULT ATTRIBUTESMODIFY DEFAULT ATTRIBUTESMODIFY DEFAULT ATTRIBUTESFOR PARTITION修改分区实际值MODIFY PARTITIONMODIFY PARTITIONMODIFY PARTITIONMODIFY SUBPARTITION移动分区MOVE PARTITIONMOVE PARTITIONMOVE SUBPARTITION命名分区RENAME PARTITIONRENAME PARTITIONRENAME PARTITIONRENAME SUBPARTITION拆分分区SPLIT PARTITION无SPLIT PARTITION截断分区TRUNCATE PARTITIONTRUNCATE PARTITIONTRUNCATE PARTITIONTRUNCATE SUBPARTITION增加分区的例子如下：alter table material_testalter partition part_04 values less than(to_date(2012-01