分区表_什么情况下使用分区表

1 簇表不能进行分区。
2 不能分割含有 LONG 或 LONG RAW 列的表。
3 索引组织表不能进行范围分区。

对大表进行分区，将有益于大表操作的性能和大表的数据的维护。官方文档说通常当表的大小超过2GB，或对于 OLTP 系统，当表的记录超过 1000 万时，都应考虑对表进行分区。

● 范围分区（RANGE PARTITION） ）
● 哈希分区（HASH PARTITION） ）
● 列表分区（LIST PARTITION） ）
● 引用分区（REFERENCE PARTITION） ）
● 复合分区 （组合分区）
● INTERVAL 分区（间隔分区）
● 系统分区

在如下几种情况下会使用到范围分区：
（1）频繁地被一个时间范围谓词扫描。
（2）维护一个时间滚动的数据窗口（Rolling Window Of Data）。
 (3) 保存历史数据的表

CREATE TABLE SALE_DATA
(SALE_ID NUMBER(5),
SALESMAN_NAME VARCHAR2(30),
SALES_AMOUNT NUMBER(10),
SALES_DATE DATE)
PARTITION BY RANGE(SALES_DATE)
(
PARTITION SALES_2009_1 VALUES LESS THAN(TO_DATE(’01/02/2009′,’DD/MM/YYYY’)) tablespace s1,
PARTITION SALES_2009_2 VALUES LESS THAN(TO_DATE(’01/03/2009′,’DD/MM/YYYY’)) tablespace s2,
PARTITION SALES_2009_3 VALUES LESS THAN(TO_DATE(’01/04/2009′,’DD/MM/YYYY’)) tablespace s3)；

HASH 分区有如下的优点：
（1） 提高了大表的高可用性和可管理性。
（2） 可以避免数据倾斜，将数据均匀地分布在多个物理设备上，最大化 I/O 吞吐量。
（3） 分区修剪和分区智能连接。
（4） 要求分区键是高基数列。
（5） 分区修剪不支持基于分区字段的范围查询，只支持等值查询或 IN-LIST 查询。

下面这种情况下，使用HASH 分区比 RANGE 分区更好：
（1） 事先不知道需要将多少数据映射到给定范围的时候。
（2） 分区的范围大小很难确定，或者很难平衡的时候。
（3） RANGE 分区使数据得到不期望的聚集时。
（4） 性能特性，如并行 DML、分区修剪和分区连接很重要的时候。

CREATE TABLE T_HASH PARTITION BY HASH(OBJECT_ID) PARTITIONS 8 AS SELECT * FROM DBA_OBJECTS;    
---按照 Oracle 官方的要求，分区的数量应该是 2 的 N 次方为合适，例如 2，4，8…，这里是 8 个分区    

---查看分区情况
SELECT * FROM DBA_TAB_PARTITIONS D WHERE D.TABLE_NAME='T_HASH' ORDER BY D.TABLE_NAME, D.PARTITION_POSITION, D.PARTITION_NAME;

 SELECT PARTITION_NAME, COUNT(*) FROM USER_EXTENTS WHERE SEGMENT_NAME = ‘T_HASH’ GROUP BY PARTITION_NAME;

上面是每个分区物理上占用的 EXTENTS 数。
SELECT COUNT(*) FROM T_HASH PARTITION(SYS_P21)
UNION ALL
SELECT COUNT(*) FROM T_HASH PARTITION(SYS_P22);

列表分区（LIST PARTITION）提供了一种按照字段的值来进行分区的方法，这种方法非常适合于有高重复率字段值的表。通过这种方法，可以非常方便地控制将某些特定的数值存放到一个分区。列表分区有如下特点：
（1） 列表值是离散的。
（2） 列表值是无序的，例如：PARTITION PART_01 VALUES('A','E','F')等。
（3） 分区键仅能包含一个列。
（4） 列表值是低基数的。

CREATE TABLE SALES_LIST
(SALESMAN_ID NUMBER(5),
SALESMAN_NAME VARCHAR2(30),
SALES_STATE VARCHAR2(20),
SALES_AMOUNT NUMBER(10),
SALES_DATE DATE)
PARTITION BY LIST(SALES_STATE)
(PARTITION SALES_WEST VALUES('CALIFORNIA', 'HAWAII') ,
PARTITION SALES_EAST VALUES('NEW YORK', 'VIRGINIA', 'FLORIDA'),
PARTITION SALES_CENTRAL VALUES('TEXAS', 'ILLINOIS') ,
PARTITION SALES_OTHER VALUES(DEFAULT)
);

如果某表按照某列分区之后，仍然较大，或者是有一些其它的需求，那么还可以通过在分区内再建子分区的方式将分区再分区，即复合分区的方式。复合分区在 Oracle 11g 之前有两种：RANGE-HASH 与 RANGE-LIST。
需要注意的是其顺序，根分区只能是 RANGE 分区，子分区可以是 HASH 分区或 LIST 分区，而 Oracle 11g 在复合分区功能这块有所增强，又推出了 RANGE-RANGE、LIST-RANGE、LIST-LIST 和 LIST-HASH，
这就相当于除 HASH 外三种分区方式的笛卡尔形式都有了，即目前一共有 6 种分区，但根分区只能是 RANGE 分区或 LIST 分区。

CREATE TABLE T_RANGE_LIST(ID INT,NAME VARCHAR2(100))
PARTITION BY RANGE(ID)
SUBPARTITION BY LIST(NAME)
(
PARTITION P1 VALUES LESS THAN(5)
(SUBPARTITION SP1 VALUES (‘A,B,C’),
SUBPARTITION SP2 VALUES (‘D,E,F’)
),
PARTITION P2 VALUES LESS THAN(10)
(
SUBPARTITION SP3 VALUES (‘A,B,C’),
SUBPARTITION SP4 VALUES (‘D,E,F’)
),
PARTITION P3 VALUES LESS THAN(15)
);

SELECT * FROM DBA_PART_TABLES D WHERE D.TABLE_NAME = ‘T_RANGE_LIST’;

由于数据量的巨大，所以表设计为每一个小时一个分区，数据库管理员日常要做的一件重复而无聊的工作就是每隔一天要生成新的 24 个分区，用以存储第二天的数据

CREATE TABLE intervalpart (c1 NUMBER, c3 DATE)
PARTITION BY RANGE (c3)
INTERVAL (NUMTOYMINTERVAL(1,’month’)) STORE IN (tbs1,tbs2,tbs3)
( PARTITION p1 VALUES LESS THAN(TO_DATE(‘1-1-2005’, ‘dd-mm-yyyy’)),
PARTITION p2 VALUES LESS THAN(TO_DATE(‘1-2- 2005′,’dd-mm-yyyy’))
);                                      —按月分割

select table_name,partition_name from user_tab_partitions where table_name=’INTERVALPART’;   —查询现在的分区表

select to_char(add_months(trunc(sysdate),-1),’yyyymm’) from dual;       —上月

select to_char(add_months(trunc(sysdate),1),’yyyymm’) from dual;        —下月

select * from INTERVALPART partition(part1);

1 系统会自动添加分区
2 有分区索引的话系统在创建分区的时候也会自动创建分区索引
3 interval 分区表判断详见 dba_part_tables.interval 和 dba_part_indexes.interval 列的内容

注意：对表的分区名称进行重命名后相应的分区索引的分区名称不会相应的改变，所以要保持它们的一致就必须对索引的分区也进行重命名。

1、添加分区 ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));  ---给SALES表添加一个P3分区

ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');  ---给SALES表的P3分区添加了一个P3SUB1子分区

注意：
（1）对于 hash 行 分区，当你执行 add partition 操作的时候，oracle 会自动选择一个分区，并重新分配部分记录到新建的分区，这也意味着有可能带来一些 IO 操作。
（2）执行 alter table 定 时未指定 update indexes 子句：
如果是 range/list 其 分区，其 local 和 索引和 global 索引不会受影响;
如果是 hash 的 分区，新加分区及有数据移动的分区的 local 和 索引和 glocal 为 索引会被置为 unuseable，需要重新编译。
（3）复合分区完全适用上述所述规则

(4)以上添加的分区界限应该高于最后一个分区界限。

2、收缩表分区(coalesce partitions)

coalesce partition 是个很有意思的分区功能，仅能被应用于 hash 分区或复合分区的 hash 子分区，执行之后，会自动收缩当前的表分区，比如某表当前有 5 个 hash 分区，执行 alter table tbname coalesce partitions 后就变成 4 个，再执行一次就变成 3 个，再执行一次就变 2 个，再执行一次就………..就报错了：），对于已分区的表至少要有一个分区存在的嘛！

alter table t_partition_hash coalesce partition;
注意，收缩的只是分区，并不会影响到数据，但是视被收缩分区中数据的多少，收缩表分区也会涉及到 IO 操作。

  3、删除分区 和删除子分区(drop partition)

ALTER TABLE SALES DROP PARTITION P3;    —删除了P3表分区：

ALTER TABLE SALES DROP SUBPARTITION P4SUB1;   —删除了P4SUB1子分区：

注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。

注意：drop 操作和 truncate 操作一样，都是对 含有数据的分区执行的话此时全局索引才会失效，对于没有数据的分区执行该操作不会影响索引的状态。

  4、截断表分区(Truncate Partition)

删除一个表的数据是 truncate table table_name;        

alter table sales3 truncate partition sp1 —这种方式会使全局分区索引无效
alter table sales3 truncate partition sp1 update global indexes —这种方式全局分区索引不会无效

ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;   —截断子分区
Truncate partition 就像 truncate table 一样，直接从头部截断数据，用来删除数据那是效率超高无比。但是如果该表有外键引用的话，ddl 的 truncate 就不好使了，这时候你只能要么使用 delete，要么先 disable 掉外键关联再 truncate 了

 5、截断带有约束的分区表

a、禁用约束
alter table sales disable constraint dname_sales1
b、截断分区
alter table sales truncate partitoin dec
c、启用约束
alter table sales enable constraint dname_sales1

注意：对分区表的某个 含有数据的分区了 执行了 truncate 操作可以导致该分区表的全局索引失效，而分区索引依然有效，这里需要注意的是前提条件：含有数据的分区，如果某个分区没有数据，即使执行了 truncate 操
作全局和分区索引都不会失效处理办法：
① 执行 truncate 语句的时候加上 update global indexes 参数
② 执行完 truncate 语句后再重建全局索引
③ 修改全局索引为分区索引

 6、移动表分区(Move Partition)

alter table t_partition_range move partition t_range_p1 tablespace tbspart02;

select partition_name,tablespace_name from user_tab_partitions where table_name=’T_PARTITION_RANGE’;

（1）move partition/subpartiton 时会锁表，对于分区表只会对操作的分区锁表，即分区消除，并且move partition/subpartiton 视被移动分区中数据量的多少，会带来相应的 IO 操作。同时还需要注意，如
果在 move partition/subpartiton 时没有指定 update indexes 子句，则被移动分区所在的 local 索引以及全局索引都会失效，需要手工 rebuilding。
（2）Move 操作会导致 rowid 改变

 7、合并分区(Merge Partitions)

alter table t_partition_range merge partitions t_range_p1,t_range_p2 into partition t_range_pnew;
注意：要合并的两个分区必须是连续的

 8、拆分分区

 拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。

ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE(‘2003-02-01′,’YYYY-MM-DD’)) INTO (PARTITION P21,PARTITION P22);

 9、接合分区(coalesca)

结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，值得注意的是，接合分区只能用于散列分区中。

ALTER TABLE SALES COALESCA PARTITION;

 10、重命名某一个分区(Rename Partition)

 alter table t_partition_range rename partition t_range_pnew to t_range_p1;

 11、分区并行度

alter table test_partition_lhr move partition pt1 compress nologging parallel 8; ;
alter index ind_xxxx rebuild partition p201401 nologging parallel 6;

1、查询分区表信息

SELECT * FROM USER_PART_TABLES where table_name=”;

2、查询当表分区信息

SELECT * FROM USER_TAB_PARTITIONS T WHERE T.TABLE_NAME=”;

3、查询某用户下二级分区的信息（只有创建了二级分区才有数据）：

SELECT * FROM USER_TAB_SUBPARTITIONS;

4、显示当前用户所有分区表的分区列信息：

select * from USER_PART_KEY_COLUMNS;

5、显示当前用户所有分区表的子分区列信息：

select * from USER_SUBPART_KEY_COLUMNS;

6、查询某分区下的数据量：

SELECT COUNT(*) FROM TABLE_PARTITION PARTITION(TAB_PARTOTION_01);

7、查询当前用户下有哪些分区索引：

SELECT * FROM USER_PART_INDEXES;

8、查询当前用户下分区索引的分区信息：

SELECT * FROM USER_IND_PARTITIONS T WHERE T.INDEX_NAME=”;

9、查询索引、表上在那些列上创建了分区：

SELECT * FROM USER_PART_KEY_COLUMNS;

（1 ）导出/ 导入方法（Export/Import Method） ） （2 ）子查询插入方法（Insert With a Subquery Method） ） （3 ）分区交换方法（Partition Exchange Method） ） （4 ）在线重定义方法（DBMS_REDEFINITION Method）

1 、导出/ 导入 方法（ （Export/Import Method） ）
采用逻辑导出/导入很简单，首先在源库建立分区表，然后将数据导出，导入到新建的分区表即可。
（1）导出表：exp usr/pswd tables=T_TEST_LHR file=exp_lhr.dmp
（2）删除表：DROP TABLE T_TEST_LHR
（3）重建分区表的定义：
CREATE TABLE T_TEST_LHR(QTY NUMBER(3), NAME VARCHAR2(15))
PARTITION BY RANGE (QTY) (PARTITION P1 VALUES LESS THAN (501), PARTITION P2 VALUES LESS THAN
(MAXVALUE));
（4）利用 ignore=y 来导入分区表：imp usr/pswd file=exp_lhr.dmp ignore=y

2 、子查询插入 方法（ （Insert With a Subquery Method） ）
主要过程如下所示，其中，T 表是非分区表：

CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

SELECT TO_CHAR(T.TIME, ‘YYYYMM’),COUNT(1) FROM T GROUP BY TO_CHAR(T.TIME, ‘YYYYMM’);

CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME) 

(PARTITION T1 VALUES LESS THAN (TO_DATE(‘201311’, ‘YYYYMM’)),

PARTITION T2 VALUES LESS THAN (TO_DATE(‘201606’, ‘YYYYMM’)),

PARTITION T3 VALUES LESS THAN (MAXVALUE)) AS SELECT ID, TIME FROM T;

RENAME T_NEW TO T;

3 、分区交换 方法（ （Partition Exchange Method） ）
主要过程有如下几个步骤：

—创建表

CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS WHERE CREATED<=to_date(‘201311′,’YYYYMM’);

commit;

—创建分区表

CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)

(PARTITION T1 VALUES LESS THAN (TO_DATE(‘2013-11-1’, ‘YYYY-MM-DD’)),

PARTITION T2 VALUES LESS THAN (MAXVALUE));

—交换数据

ALTER TABLE T_NEW EXCHANGE PARTITION T1 WITH TABLE T;   

—改变表名

RENAME T TO T_OLD;

RENAME T_NEW TO T;

—验证

SELECT TO_CHAR(T.TIME, ‘YYYYMM’), COUNT(1) FROM T GROUP BY TO_CHAR(T.TIME, ‘YYYYMM’);

4、  在线重定义 方法（ （DBMS_REDEFINITION Method）

主要过程如下所示：

–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 P20070201 VALUES LESS THAN (TO_DATE(‘2007-2-1’, ‘YYYY-MM-DD’)), 

     PARTITION P20070301 VALUES LESS THAN (TO_DATE(‘2005-3-1’, ‘YYYY-MM-DD’)),

     PARTITION P20070401 VALUES LESS THAN (TO_DATE(‘2005-4-1’, ‘YYYY-MM-DD’)), 

     PARTITION P20070501 VALUES LESS THAN (TO_DATE(‘2005-5-1’, ‘YYYY-MM-DD’)),  

     PARTITION P20070601 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 <tablename>;

drop materialized view log on t;

    OR drop materialized t;

1、单分区查询
select * * from TEST_PARTITION_LHR partition( (pt4) ) t where t.created >=sysdate- -1;
select * * from TEST_PARTITION_LHR_SUB partition( (pt4) ) t where t.created >=sysdate- -1;
select * * from TEST_PARTITION_LHR_SUB subpartition( (SYS_SUBP35) ) t where t.created >=sysdate- -1;
2、跨分区查询
（1） 加上分区键的限制条件
（2） 建立全局索引---不可取
（3） 利用 union all 合并需要的分区
多个分区查询需要用分区关键字作为限制条件，才可以避免分区表的全分区扫描，一般情况下都可以根据分区关键字找到结果集的大致区间，用这个作为限制条件避免分区表的全分区扫描。
select * * from TEST_PARTITION_LHR t where t.object_name like 'LHR%' and t.created >=sysdate- -2 2; ; --- 分区关键字为 created
另一种避免全分区扫描的办法就是建立全局索引，然后利用全局索引则可以避免分区表的全分区扫描，但如果是在分区键上建立的全局索引可取，在非分区键上建立的全局索引不可取，效率低下

相关的数据字典：
select * from dba_lob_partitions D where D.T;
select * from dba_part_lobs;
select * from dba_lobs d where d.TABLE_NAME='XB_AUDIT_DDL_LHR';
select * from dba_lob_templates;
select * from dba_lob_subpartitions;

在使用分区表的时候需要注意以下几方面的内容：
1 在查询分区表时尽量带上分区键过滤条件，否则可能引起全分区扫描。
2 在设计分区表时，避免数据都进入默认分区，从而导致出现默认分区超大或 各个分区大小严重不均衡的情况，失去分区表的意义。
3 需要特别注意分区表性能比普通表性能差的情况。这种情况的本质原因是，虽然分区表的分区索引比全局索引要小很多，但是由于没有扫描指定的分区，而是扫描了很多个小的索引，这些小索引的高度
累计起来一般都比全局索引要高。索引的范围检索性能是由索引的高度（BLEVEL）决定的，而不是由索引的大小决定。所以，性能差异很明显