什么是分区表_什么是分区表

文章目录

分区表（谨慎使用，了解即可）

表空间及分区表的概念

表空间（类似于命名空间）：是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，但主要存放的是表，所以称作表空间。

分区表：当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。对表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。

表分区的具体作用

Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外，分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。

分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。

从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。

从应用程序的角度来看，分区后的表与非分区表完全相同，使用 DML 命令访问分区后的表时，无需任何修改。

什么时候需要分区

什么时候对表进行分区,我们可以从下面的条件进行考虑：
1、表的大小超过2GB；
2、对一个表并行进行DML操作；
3、为了平衡硬盘I/O，需将同一个表分区到不同的表空间，必须对表进行分区；
4、要将表的一部分设为只读状态，另一部分设为读写状态，需要对表进行分区；
5、要将表一部分设为可用状态，另一部分设为不可用状态，需要队标进行分区；
6、要将表中数据按照一定规则分散到不同的磁盘中去，需要对表进行分区；
7、表里包含大量的历史数据，这样就可以用分区表，新的数据放入新的分区。历史数据可以定期转历史。
注意：分区依赖列不能是lob，long之类数据类型，每个表的分区或子分区数的总数不能超过1023个。

表分区的优点

1、提高数据可管理性。对表进行分区，数据的加载、索引的创建与重建、数据的备份与恢复等操作都可以在分区表上进行，而不必在表级别上进行，提高了数据的可管理性；
2、增强数据库的可用性。某个分区出现问题，只影响该分区，其它分区照常运作；
3、改善查询性能。将对整个表的查询转化为对某个分区表的查询，提高了查询速度；
4、提高数据库操作的并行性。可对分区表进行并行操作；
5、透明性。将一张表分区后，对于用户而言是无感的，即用户不会感知到有多个分区表的存在，用户不需要对SQL语句做处理；
6、均衡I/O：可以将分区映射到不同的物理磁盘上，来分散IO;

表分区的几种类型及操作方法

范围分区(range)

范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。

当使用范围分区时，请考虑以下几个规则：
1、每一个分区都必须有一个VALUES LESS THAN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
2、所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。
3、在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THAN的值，同时包括空值。

/*例一：假设有一个CUSTOMER表，表中有数据200000行，我们将此表通过CUSTOMER_ID进行分区， 每个分区存储100000行，我们将每个分区保存到单独的表空间中，这样数据文件就可以跨越多个物理磁盘。*/
CREATE TABLE CUSTOMER     --建CUSTOMER表
( 
	CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
	FIRST_NAME  VARCHAR2(30) NOT NULL, 
	LAST_NAME   VARCHAR2(30) NOT NULL, 
	PHONE        VARCHAR2(15) NOT NULL, 
	EMAIL        VARCHAR2(80), 
	STATUS       CHAR(1) 
) 
PARTITION BY RANGE (CUSTOMER_ID)     --根据CUSTOMER_ID整型列进行分区
( 
    PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01, 
    PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02 
);

--例二：按时间划分
CREATE TABLE ORDER_ACTIVITIES     --建ORDER_ACTIVITIES表
( 
    ORDER_ID      NUMBER(7) NOT NULL, 
    ORDER_DATE    DATE, 
    TOTAL_AMOUNT NUMBER, 
    CUSTOTMER_ID NUMBER(7), 
    PAID           CHAR(1) 
) 
PARTITION BY RANGE (ORDER_DATE)      --根据ORDER_DATE日期类型列进行分区 
(
	PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('01- MAY -2003','DD-MON-YYYY')) TABLESPACEORD_TS01,
	PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) TABLESPACE ORD_TS02,
	PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY')) TABLESPACE ORD_TS03
);

--例三：使用MAXVALUE
CREATE TABLE RangeTable
( 
	idd   INT PRIMARY KEY , 
	iNAME VARCHAR(10), 
	grade INT  
) 
PARTITION  BY  RANGE (grade) 
( 
	PARTITION  part1 VALUES  LESS  THAN (1000) TABLESPACE  Part1_tb, 
	PARTITION  part2 VALUES  LESS  THAN (MAXVALUE) TABLESPACE  Part2_tb 
);

--在建分区的时候，也可以建立联合分区
create table range_part_mult_col_tab 
(
	id number,
	deal_date date,
	area_code number,
	nbr number,
	contents varchar2(4000)
)
partition by range (area_code,deal_date)
(
	partition p_591_201901 values less than (591,TO_DATE('2019-02-01', 'YYYY-MM-DD')),
	partition p_591_201902 values less than (591,TO_DATE('2019-03-01', 'YYYY-MM-DD')),
	partition p_593_max values less than (593,maxvalue)
);

哈希分区(hash)

对于那些无法有效划分范围的表，可以使用hash分区

hash分区最主要的机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中，hash算法中最重要的是hash函数，Oracle中如果你要使用hash分区，只需指定分区的数量即可。

建议分区的数量采用2的n次方，这样可以使得各个分区间数据分布更加均匀。

这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。
散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。

CREATE TABLE HASH_TABLE 
( 
  COL NUMBER(8), 
  INF VARCHAR2(100) 
) 
PARTITION BY HASH (COL) 
( 
  PARTITION PART01 TABLESPACE HASH_TS01, 
  PARTITION PART02 TABLESPACE HASH_TS02, 
  PARTITION PART03 TABLESPACE HASH_TS03 
);

--简写：
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);

列表分区(list)

该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。
通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录。
如果没有default分区，当不满足规则的时候，就会报错 ORA0-14400：插入的分区关键字未映射到任何分区。

--例一
CREATE TABLE PROBLEM_TICKETS 
( 
    PROBLEM_ID   NUMBER(7) NOT NULL PRIMARY KEY, 
    DESCRIPTION  VARCHAR2(2000), 
    CUSTOMER_ID  NUMBER(7) NOT NULL, 
    DATE_ENTERED DATE NOT NULL, 
    STATUS       VARCHAR2(20) 
) 
PARTITION BY LIST (STATUS) 
( 
      PARTITION PROB_ACTIVE   VALUES ('ACTIVE') TABLESPACE PROB_TS01, 
      PARTITION PROB_INACTIVE VALUES ('INACTIVE') TABLESPACE PROB_TS02,
      PARTITION PROB_DEFAULT  VALUES (default) TABLESPACE PROB_TS03
);
      
--例二
CREATE  TABLE  ListTable
( 
    id    INT  PRIMARY  KEY , 
    name  VARCHAR (20), 
    area  VARCHAR (10) 
) 
PARTITION  BY  LIST (area)    
( 
    PARTITION  part1 VALUES ('guangdong','beijing') TABLESPACE  Part1_tb, 
    PARTITION  part2 VALUES ('shanghai','nanjing')  TABLESPACE  Part2_tb 
);

虚拟列分区

更详细见：https://www.cnblogs.com/moonandstar08/p/5100567.html

Oracle11g 增加了虚拟列的新特性，具体说明如下：

只能在堆组织表（普通表）上创建虚拟列，不能在索引表、外部表、临时表上创建虚拟列；
如果在已经创建的表中增加虚拟列时，若没有指定虚拟列的字段类型，ORACLE会根据 generated always as后面的表达式计算的结果自动设置该字段的类型
虚拟列不能是LOB或者RAW类型；
虚拟列的值并不是真实存在的，只有用到时，才根据表达式计算出虚拟列的值，磁盘上并不存放。虚拟列的值由ORACLE根据表达式自动计算得出，不可以做UPDATE和INSERT操作，
可以对虚拟列做 DELETE 操作。
可在虚拟列上建立索引：create index inx_test on test1(v1);
可把虚拟列当做分区关键字建立分区表，这是ORACLE 11g的另一新特性——虚拟列分区

CREATE TABLE PT
(
	getdate date NOT NULL,
	--虚拟列wd是number型,该虚拟列的值是根据后面表达式计算得到的
	wd NUMBER GENERATED ALWAYS AS(TO_NUMBER(TO_CHAR (getdate,'D'))) VIRTUAL  
)
PARTITION BY LIST(wd)    --创建带有虚拟列的分区
(
	PARTITION sun VALUES (1),
	PARTITION Mon VALUES (2),
	PARTITION Tue VALUES (3),
	PARTITION wed VALUES (4),
	PARTITION Thu VALUES (5),
	PARTITION Fri VALUES (6),
	PARTITION sat VALUES (7)
) ;

--插入测试数据
insert into pt(getdate) values(sysdate);
insert into pt(getdate) values(sysdate-1);
insert into pt(getdate) values(sysdate-2);
insert into pt(getdate) values(sysdate-3);
insert into pt(getdate) values(sysdate-4);
insert into pt(getdate) values(sysdate-5);
insert into pt(getdate) values(sysdate-6);
insert into pt(getdate) values(sysdate-7);
commit;

Interval分区

更详细见：https://blog.csdn.net/weixin_30895723/article/details/104257825

使用Oracle Database 11g可以创建新类型的Interval分区表，Interval类型分区表，可以根据加载数据，自动创建指定间隔的分区。
在创建自动间隔分区的时候，最为核心的就是INTERVAL关键字了。
对于按照时间进行自动分区，INTERVAL后面可以跟随NUMTOYMINTERVAL和NUMTODSINTERVAL。两个关键字用法如下：

1、NUMTOYMINTERVAL(x,c)
用法：x是一个数据，c是一个字符串，该函数是将x转为interval year to month类型。常用单位有：year、month。

---例子：当前时间加3年，当前时间加3个月
SELECT SYSDATE,
	SYSDATE + NUMTOYMINTERVAL(3,'year')  AS "3年后" ,
	SYSDATE + NUMTOYMINTERVAL(3,'month') AS "3个月后"
FROM   dual;

--创建按月分区的分区表：
create table intervalpart
(
	c1 number, 
	c3 date
)
partition by range (c3)
interval(numtoyminterval(1,'MONTH'))    --在插入相关数据后会自动生成分区
(
	partition part1 values less than (to_date('01/01/2015','MM/DD/YYYY')),
);

/* 查看现在表的分区 select table_name, partition_name from user_tab_partitions where table_name = 'INTERVALPART; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ INTERVALPART PART1 */

/* 向intervalpart 表中插入数据，如果符合分区条件，会自动生成分区 begin for i in 0..6 loop insert into intervalpart values(i, add_months(to_date('2015-1-1','yyyy-mm-dd'),i)); end loop ; commit; end; */

/* --观察自动创建的分区，由oracle提供分区的名称 select table_name, partition _name from user_tab_partitions where table_name = 'INTERVALPART'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ INTERVALPART PART1 INTERVALPART SYS_P41 INTERVALPART SYS_P42 INTERVALPART SYS_P43 INTERVALPART SYS_P44 INTERVALPART SYS_P45 INTERVALPART SYS_P46 */

2、NUMTODSINTERVAL(x,c)
用法：x是一个数据，c是一个字符串，该函数是将x转为interval day to second类型。常用单位有：day、hour、minute、second。

--创建一个以天为间隔的分区表
create table sales
(
	sales_id    number,
	sales_dt    date
)
partition by range (sales_dt)
interval(numtoyminterval(1,'day'))
(
	partition p090101 values less than (to_date('2009-01-01','yyyy-mm-dd'))
);

--查询表名，以及对应的分区名
select table_name, partition_name from user_tab_partitions;

TABLE_NAME                    PARTITION_NAME
------------------------------ ------------------------------
SALES                          P090101

-- 按周创建分区
CREATE TABLE interval_week_table01
(
	employee_id         NUMBER,
	employee_name       VARCHAR2(20),
	birthday            DATE    
)
PARTITION BY RANGE(birthday)
INTERVAL (NUMTODSINTERVAL(7,'day')) STORE IN (tbs01,tbs02,tbs03)
(
	PARTITION partition201401w VALUES LESS THAN(to_date('2014-01-07 00:00:00','yyyy-mm-dd hh24:mi:ss'))
);

组合分区

Oracle 11g之前只有两种组合分区range-hash，range-list，11g之后新增四种组合分区range-range，list-range，list-list，list-hash。组合中第一个是根分区，第二个是子分区，顺序不能变。

范围–列表复合分区(range-list）

这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区。

1、写完整所有分区

CREATE TABLE SALES     --建表
(
	PRODUCT_ID VARCHAR2(5),
	SALES_DATE DATE,
	SALES_COST NUMBER(10),
	STATUS VARCHAR2(20)
)
PARTITION BY RANGE(SALES_DATE)     --根分区
SUBPARTITION BY LIST (STATUS)      --子分区
(
	PARTITION P1 VALUES LESS THAN(TO_DATE('2003-01-01','YYYY-MM-DD'))TABLESPACE rptfact2009 
	( 
		SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009, 
		SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009 
	), 
	PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE rptfact2009 
	( 
		SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009, 
		SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009 
	) 
);

2、使用模板

CREATE TABLE SALES     --建表
(
	PRODUCT_ID VARCHAR2(5),
	SALES_DATE DATE,
	SALES_COST NUMBER(10),
	STATUS VARCHAR2(20)
)
PARTITION BY RANGE(SALES_DATE)     --根分区
SUBPARTITION BY LIST (STATUS)      --子分区
SUBPARTITION TEMPLATE              --子分区模板
( 
	SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009, 
	SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009 
)
(
	PARTITION P1 VALUES LESS THAN(TO_DATE('2003-01-01','YYYY-MM-DD'))TABLESPACE rptfact2009 
	PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE rptfact2009 
);

范围–哈希复合分区(range-hash)

这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区。

create table dinya_test 
( 
	transaction_id number primary key, 
	item_id number(8) not null, 
	item_description varchar2(300), 
	transaction_date date 
) 
partition by range(transaction_date)
subpartition by hash(transaction_id)  
subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03) 
( 
	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) 
);

普通表转分区表的方法

更详细见：https://www.cnblogs.com/lhrbest/articles/5543845.html

将普通表转换成分区表有4种方法：
1）Export/import method
2）利用原表重建分区表(插入)
3）Partition exchange method
4）DBMS_REDEFINITION

Export/import method

采用逻辑导出导入很简单，首先在源库建立分区表，然后将数据导出，然后导入到新建的分区表即可

1）导出表：exp usr/pswd tables=numbers file=exp.dmp
2）删除表：drop table numbers;
3）重建分区表的定义举例：
	create table numbers (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.dmp ignore=y

举例：

--创建普通表并插入测试数据
CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;
commit;

select to_char(t.time, 'YYYYMM'), COUNT(1) from t group by to_char(t.time, 'YYYYMM');
/* TO_CHA COUNT(1) ------ ---------- 201310 85984 201605 1107 */

--采用expdp导出表
expdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=lhr_t.dmp  INCLUDE=TABLE:\"IN \'T\'\" SCHEMAS=LHR LOGFILE=expdp_T.log

--删除原表，创建一个分区表结构：
drop table t;
CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE )
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)
);

--导入到分区表
impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=lhr_t.dmp SCHEMAS=LHR table_exists_action=APPEND LOGFILE=impdp_T.log

--查询导入后的情况：
SELECT D.TABLE_OWNER, D.TABLE_NAME, D.PARTITION_NAME FROM DBA_TAB_PARTITIONS d WHERE d.table_name='T';
/* TABLE_OWNER TABLE_NAME PARTITION_NAME --------------- ------------------- ---------------------- LHR T T1 LHR T T2 LHR T T3 */

利用原表重建分区表(插入)

优点：方法简单易用，由于采用DDL语句，不会产生UNDO，且只产生少量REDO，效率相对较高，而且建表完成后数据已经在分布到各个分区中了。

不足：对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性，在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失，如果要保证一致性，需要在执行完语句后对数据进行检查，而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。

适用于修改不频繁的表，在闲时进行操作，表的数据量不宜太大。

主要有2种方式，ctas和insert方式，下边分别介绍：

CTAS+RENAME

CTAS语法: CREATE TABLE AS statement
建立分区表时，使用CTAS语法将普通表的数据插入到新的分区表中。CTAS这种方法采用DDL语句，不产生UNDO，只产生少量REDO，建表完成后数据已经在分布到各个分区中。

--创建普通表并插入测试数据
CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

--创建一个分区表，注意这里的分区表的列后边没有数据类型：
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;    --使用CTAS语法将普通表的数据插入到新的分区表中

--改变表名
rename t_new to t;

--验证新表数据
select to_char(t.time, 'YYYYMM'), COUNT(1) from t group by to_char(t.time, 'YYYYMM');
/* TO_CHA COUNT(1) ------ ---------- 201310 85984 201605 1107 */

Insert with a subquery method

建立分区表，使用insert语句将普通表的数据插入到新的分区表中。

--创建普通表T_LHR_20160527并插入测试数据
CREATE TABLE T_LHR_20160527 (ID NUMBER PRIMARY KEY, TIME DATE);
INSERT INTO T_LHR_20160527 SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

--创建一个分区表T_LHR_20160527_NEW：有数据类型
CREATE TABLE T_LHR_20160527_NEW (ID NUMBER, TIME DATE)
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)
);

--从源表查询插入到新表中
/*alter table T_LHR_20160527_NEW nologging; alter session enable parallel dml;*/
insert /*+APPEND PARALLEL*/ into T_LHR_20160527_NEW (ID, TIME) select * from T_LHR_20160527;
commit;

--删除源表，重命名新表
drop table T_LHR_20160527;
rename T_LHR_20160527_NEW to T_LHR_20160527;

--验证新表数据
select to_char(t.time, 'YYYYMM'), COUNT(1) from T_LHR_20160527 t group by to_char(t.time, 'YYYYMM');
/* TO_CHA COUNT(1) ------ ---------- 201310 85984 201605 1107 */

使用交换分区的方法(Partition exchange method)

只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。

语法：alter table tbname1 exchange partition/subpartition ptname with table tbname2;
含义：将表tbname1中的ptname分区与表tbname2进行交换
实质：只是交换了名称，类似于指针的指向的内容发生了变化。
注意事项：一旦附加了without validation子句，则表示不再验证数据有效性，因此指定该子句时务必慎重。
1）涉及交换的两表之间表结构必须一致，除非附加with validation子句;
2）如果是从非分区表向分区表做交换，非分区表中的数据必须符合分区表中指定分区的规则，除非附加without validation子句;
3）如果从分区表向分区表做交换，被交换的分区的数据必须符合分区规则，除非附加without validation子句;
4）Global索引或涉及到数据改动了的global索引分区会被置为unusable，除非附加update indexes子句。
使用场景：适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。

单个分区示例

--创建分区表，假设有2个分区，T1,T2
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)
);

--创建普通表T 并插入存放分区T1规则的数据
CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS where CREATED<=to_date('201311','YYYYMM');

--交换数据
ALTER TABLE T_NEW EXCHANGE PARTITION T1 WITH TABLE T; --把表T的数据放到到T1分区

--改变表名
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');
/* TO_CHA COUNT(1) ------ ---------- 201310 85984 */

多个分区示例

交换分区的操作步骤如下：
1）创建分区表，假设有2个分区，P1，P2。
2）创建表A存放P1规则的数据。
3）创建表B存放P2规则的数据。
4）用表A 和P1 分区交换， 把表A的数据放到到P1分区。
5）用表B 和p2 分区交换， 把表B的数据存放到P2分区。

--创建分区表，假设有2个分区，emp_p1，emp_p2 
CREATE TABLE p_emp(sal NUMBER(7,2))
PARTITION BY RANGE(sal)
(
	partition emp_p1 VALUES LESS THAN (2000),  
	partition emp_p2 VALUES LESS THAN (4000)
);

-- 创建普通表
CREATE TABLE exchtab1 as SELECT sal FROM emp WHERE sal<2000; --创建表exchtab1存放emp_p1规则的数据。
CREATE TABLE exchtab2 as SELECT sal FROM emp WHERE sal BETWEEN 2000 AND 3999; ----创建表exchtab2存放emp_p2规则的数据。

--交换数据
alter table p_emp exchange partition emp_p1 with table exchtab1; --把表exchtab1的数据放到到emp_p1分区
alter table p_emp exchange partition emp_p2 with table exchtab2; --把表exchtab2的数据放到到emp_p2分区

利用在线重定义功能(DBMS_REDEFINITION)

优点：保证数据的一致性，在大部分时间内，表T都可以正常进行DML操作。只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。

不足：实现上比上面两种略显复杂。

适用于各种情况。

在线重定义的大致操作流程如下：
1）创建基础表A，如果存在，就不需要操作。
2）创建临时的分区表B结构。
3）开始重定义，将基表A的数据导入临时分区表B。
4）结束重定义，完成后在DB的 Name Directory里，已经将2个表进行了交换。即此时基表A成了分区表，我们创建的临时分区表B 成了普通表。此时我们可以删除我们创建的临时表B。它已经是普通表。

分区表的其它操作

添加新的分区

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

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

如果往range分区表(存在maxvalue分区)或者list分区表(存在default分区)中添加新的分区时，需要：
1、先删除maxvalue分区或者default分区
2、添加新的分区
3、添加maxvalue分区或者default分区

--建表和建分区
create table ahzhixin(
	id varchar2(15 byte) not null,
	areacode varchar2(4 byte)
)
partition by list (areacode)(
	partition t_list556 values ('0556'),
	partition p_other values (default)
);

--插入测试数据
insert into ahzhixin values('1','0556');
insert into ahzhixin values('2','0551');
insert into ahzhixin values('3','0555');

--删除default分区
alter table ahzhixin drop partition p_other;

--添加新分区
alter table ahzhixin add partition t_list551 values('0551');

--添加default分区
alter table ahzhixin add partition p_other values (default) ;

split 分区拆分

拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。
如果是Range类型的，使用at，List使用values

ALTER TABLE SALES SPLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);

合并分区Merge

合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。以下代码实现了P1 P2分区的合并：

ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;

接合分区(coalesca)

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

ALTER TABLE SALES COALESCA PARTITION;

移动分区

alter table ahzhixin move partition P_OTHER tablespace system;
alter table ahzhixin move partition P_OTHER tablespace users;

Truncate分区

截断某个分区是指删除某个分区中的数据，并不会删除分区。
Truncate后数据再也找不回了，需要谨慎使用。Delete数据却可以通过FlashBack技术找回。

--截断分区
ALTER TABLE SALES TRUNCATE PARTITION P2;

--截断子分区
ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;

Drop分区

--删除P3表分区
ALTER TABLE SALES DROP PARTITION P3;

--删除P4SUB1子分区
ALTER TABLE SALES DROP SUBPARTITION P4SUB1;

重命名表分区

--以下代码将P21更改为P2
ALTER TABLE SALES RENAME PARTITION P21 TO P2;

查看分区表、分区、子分区信息

-- 跨分区查询数据
select sum(*) from
(select count(*) cn from t_table_SS PARTITION (P200709_1)
union all
select count(*) cn from t_table_SS PARTITION (P200709_2));

--查询表上有多少分区
SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='tableName'

--查询索引信息
select object_name,object_type,tablespace_name,sum(value)
from v$segment_statistics
where statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX'
group by object_name,object_type,tablespace_name
order by 4 desc

与分区相关的数据字典：

数据字典	说明
xxx_PART_TABLES	包含分区表的分区信息
xxx_TAB_PARTITIONS	包含分区层次、分区存储、分区统计等信息
xxx_TAB_SUBPARTITIONS	包含子分区层次、分区存储、分区统计等信息
xxx_PART_KEY_COLUMNS	包含分区表的分区列信息
xxx_SUBPART_KEY_COLUMNS	包含子分区表的分区列信息
xxx_COL_STATISTICS	包含分区表的分区列的统计信息和柱状图信息
…	…

xxx：代表DBA、ALL、USER
DBA_PART_TABLES：查看数据库里面的全部分区表信息，需要具有DBA权限，否则会报“ora-00942: 表或视图不存在”错误；
ALL_PART_TABLES：查看当前用户可见的全部分区表信息；
USER_PART_TABLES：查看当前用户拥有的全部分区表信息；

今天的文章什么是分区表_什么是分区表分享到此就结束了，感谢您的阅读。

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至举报，一经查实，本站将立刻删除。
如需转载请保留出处：https://bianchenghao.cn/79997.html