表分区和索引分区

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 DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'T', DBMS_REDEFINITION.CONS_USE_PK); （如果没有定义主键会提示以下错误信息 begin dbms_redefinition.can_redef_table(user,'pft_party_profit_detail'); end; ORA-12089: cannot online redefine table "OFSA"."PFT_PARTY_PROFIT_DETAIL" with no primary key ORA-06512: at "SYS.DBMS_REDEFINITION", line 8 ORA-06512: at "SYS.DBMS_REDEFINITION", 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 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),  PARTITION P4 VALUES LESS THAN (MAXVALUE)); 这里值得注意的一个问 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 是：PARTITION P4 VALUES LESS THAN (MAXVALUE))是把所有剩下的数据分在一个区里，如果你想一个月建一个分区，那最好写成这样，提前建好，目前我还不知道能不能自动建，如果哪位知道请告知我，谢谢   SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)  (PARTITION P2******* VALUES LESS THAN (TO_DATE('2007-2-1', 'YYYY-MM-DD')),  PARTITION P2******* VALUES LESS THAN (TO_DATE('2005-3-1', 'YYYY-MM-DD')), PARTITION P2******* VALUES LESS THAN (TO_DATE('2005-4-1', 'YYYY-MM-DD')),  PARTITION P2******* VALUES LESS THAN (TO_DATE('2005-5-1', 'YYYY-MM-DD')),   PARTITION P2******* VALUES LESS THAN (TO_DATE('2005-6-1', 'YYYY-MM-DD')));   5、执行表的在线重定义： SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T', 'T_NEW'); 6、执行把中间表的内容和数据源表进行同步。 SQL>execute dbms_redefinition.sync_interim_table(user,'t','t_new'); 7、执行结束在线定义过程 SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(user, 'T', 'T_NEW'); 8、查看数据字典，可以看到改表已经成为了分区表。 SQL> SELECT COUNT(*) FROM T; COUNT(*) ---------- 6264 SQL> SELECT COUNT(*) FROM T PARTITION (P2); COUNT(*) ---------- 6246 SQL> SELECT COUNT(*) FROM T PARTITION (P3); COUNT(*) ---------- 至此普通表转为分区操作完成 9、如果执行在线重定义的过程中出错 可以在执行dbms_redefinition.start_redef_table之后到执行dbms_redefinition.finish_redef_table之前的时间里执行：DBMS_REDEFINITION.abort_redef_table(user, 't', 't_new')以放弃执行在线重定义。 10、如果出现以下错误： SQL> exec dbms_redefinition.can_redef_table(user, 't'); BEGIN dbms_redefinition.can_redef_table(user, 't'); END; * ERROR at line 1: ORA-12091: cannot online redefine table "user"."t" with materialized views ORA-06512: at "SYS.DBMS_REDEFINITION", line 137 ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478 ORA-06512: at line 1 用这句删除materialized view 即可继续进行 drop materialized view log on ; drop materialized view log on t; OR drop materialized t; 举个在线重定义使用的例子： SQL> create table test_primary (id number primary key); 表已创建。 SQL> insert into test_primary select rownum from dba_objects; 已创建6264行。 SQL> commit; 提交完成。 SQL> create table orgin_table (id number, fid number, name varchar2(30), create_date date) 2  tablespace users; 表已创建。 SQL> insert into orgin_table select rownum, rownum, object_name, created from dba_objects 2  where owner = 'SYS'; 已创建4034行。 SQL> commit; 提交完成。 SQL> alter table orgin_table add constraint pk_orgin_table primary key (id); 表已更改。 SQL> create or replace trigger tr_orgin_table 2  before insert on orgin_table for each row 3  begin 4  null; 5  end; 6  / 触发器已创建 SQL> create index ind_orgin_table_create_date on orgin_table(create_date); 索引已创建。 SQL> alter table orgin_table add constraint f_orgin_primary_id foreign key (fid) 2  referencestest_primary (id);