表分区和索引分区.docx

Oracle 8i 以后推出了分区选项,分区将表分离在若干不同的表空间上,用分而治之的方法来支撑无限膨胀的大表,根据大表在物理一级的可管理性,将大表分割成较小的分区可以改善表的维护、备份、恢复、事务和查询性能。
分区的具体优点:
1、增强可用性:如果表的一个分区由于系统故障而不能使用,表的其余好的分区仍可以使用。
2、减少关闭时间:如果系统故障只影响表的一部分分区,那么只有这部分分区需要修复,可能比整个大表修复花的时间更少。
3、维护轻松:如果需要建表,单独管理每个分区比单独管理单个大表要轻松得多。
4、均衡I/O:可以把表的不同分区分配到不同个磁盘来平衡I/O来改善性能。
5、改善性能:对大表的查询、修改、增加等操作可以分解到表的不同分区来并行执行,可使运行速度更快,在数据仓库的TP查询特别有用。
6、分区对用户透明,用户感觉不到分区的存在。
分区的分类
(1) 范围分区范围分区就是对数据表中的某个值的范围进行分区,根据某个值的范围,决定将该数据存储在哪个分区上。如根据序号分区,根据业务记录的创建日期进行分区等。每次插入数据的时候,系统将根据指定的字段的值来自动将记录存储到指定的分区(表空间)中。
需求描述:有一个物料交易表,表名:material_transactions。该表将来可能有千万级的数据记录数。要求在建该表的时候使用分区表。这时候我们可以使用序号分区三个区,每个区中预计存储三千万的数据,也可以使用日期分区,如每五年的数据存储在一个分区上。
(2) Hash分区/散列分区散列分区为通过指定分区编号来均匀分布数据的一种分区类型,因为通过在 I/O 设备上进行散列分区,使得这些分区大小一致。
(3)list分区(列表分区) 当你需要明确的控制如何将行映射到分区时,就使用列表分区方法,与范围分区和散列分区不同,列表分区不支持多列分区,如果要将表按列分区,那么分区键就只能由表的一个单独的列组成,然而可以用范围分区或散列分区方法进行分区的所有的列,都可以用列表分区的方法进行分区。
(4) 复合分区有时候我们需要根据范围分区后,每个分区内的数据再散列地分布在几个表空间中,这样我们就要使用复合分区。复合分区是先使用范围分区,然后在每个分区内再使用散列分区的一种分区方法。(例子:将物料交易的记录按时间分区,然后每个分区中的数据分三个子分区,将数据散列地存储在三个指定的表空间中)
1、创建表
SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
2、插入数据
SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;
SQL> COMMIT;
3、在线重定义的表自行验证,看该表是否可以重定义,
SQL> EXEC (user, 'T', );
 (如果没有定义主键会提示以下错误信息
 begin (user,'pft_party_profit_detail'); end;
 ORA-12089: cannot online redefine table "OFSA"."PFT_PARTY_PROFIT_DETAIL" with no primary key
 ORA-06512: at "", line 8
 ORA-06512: at "", line 247
 ORA-06512: at line 1
 
 出错了, 该表上缺少主键,为该表建主键。再执行验证。
 SQL> alter table t add constraint pk_t primary key(id);
 Table altered)
4、建个和源表表结构一样的分区表,作为中间表。按日期范围分区
 
SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME) 
     (PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')), 
     PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')), 
     PARTITION P3 VALU