Oracel 表分区

部分内容引自

 http://blog.csdn.net/pingpinghi/article/details/4043744

 http://www.cnblogs.com/wuyisky/articles/928619.html

 

最近，因为项目部分数据表过大，有的单表数据超过千万，影响了查询速度，考虑使用分区。查询了Oracle分区资料，对单表数据按时间跨度做下分区，在当前表空间对现有表做：

create table T_ORDER_NEW

partition by range(orderdate)

(partition P_ORDER_2011 values less than(to_date('2012-01-01','yyyy-mm-dd')),

partition P_ORDER_2012 values less than(to_date('2013-01-01','yyyy-mm-dd')),

partition P_ORDER_2013 values less than(to_date('2014-01-01','yyyy-mm-dd')),

……

partition P_ORDER_2020 values less than(to_date('2021-01-01','yyyy-mm-dd')),

partition P_ORDER_MAX  values less than(MAXVALUE))

as select id,productid,unitprice,quantity,discount,orderdate from T_ORDER;

 

新的分区也可以放在别的表空间下，如

partition part1 values less then(100) tablespace part1_tb

 

分区常用操作：

--更改表名

rename t_order to t_order_bak

rename t_order_new to t_order

--查询测试

-----全部

select count(*) from t_order

----- 分区

select count(*) from t_order partition(P_ORDER_2011)

-----查询表分区情况

select table_name,partition_name,high_value,num_rows from user_tab_partitions order by table_name,partition_name

-----添加分区

alter table t_order add partition P_ORDER_2011 values less than(to_date('2011-01-01','yyyy-mm-dd'))

-----删除分区(分区数据同时删除)

alter table t_order drop partition P_ORDER_2013

-----截断分区(只删除分区数据，不删除表分区)

alter table t_order truncate P_ORDER_2011

-----合并分区(如果删除分区但不删除数据，使用merge partition)

 alter table t_order merge partitions P_ORDER_2011,P_ORDER_2012 into parititon  P_ORDER_2013

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

alter table t_order split partition P_ORDER_2011 at (to_date('2011-01-01','yyyy-mm-dd'))

into (partition P_ORDER_2011_1, partition P_ORDER_2011_2)

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

alter able t_order_hash coalesca partition;

----重命名表分区

alter table t_order rename partition P_ORDER_2011_1 to P_ORDER_2010

 

 ----回收站操作

数据表或分区删除后就像windows系统一样，在回收站里还可以看到。Oracle在10g可以在DROP表之后通过FLASHBACK TABLE命令将表恢复到删除以前的状态，而对应的清除回收站的功能就是PURGE。FLASHBACK还有RENAME TO语句，可以在还原的时候对表进行重命名，避免和当前用户下已经存在的表冲突。

SQL>show recyclebin     或者 select * from recyclebin

清除表       purge table t_tablename

清空回收站  purge recyclebin

恢复           flashback table tablename before drop (rename to old_tablename)

 

 

分区通常分为区间范围RANGE、散列分区HASH、列表分区LIST、组合分区。t_order表采用的就是按时间范围分区的，分区数据放在同一分区是因为不用修改现有程序，又能提升数据访问速度。

1.范围分区：这种类型的分区是使用列的一组值，通常将该列成为分区键。
示例1：假设有一个CUSTOMER表，表中有数据200000行，我们将此表通过CUSTOMER_ID进行分区，每个分区存储100000行，我们将每个分区保存到单独的表空间中，这样数据文件就可以跨越多个物理磁盘。下面是创建表和分区的代码，如下：
CREATE TABLE 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)
(
PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01,
PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02
)

2.列表分区：该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。
示例1：
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
)

3、散列分区：这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。请看下列示例：
示例1：
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
)

4、复合范围列表分区：这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区。
示例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 P1_TS
(
SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE SUBP1_TS1,
SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE SUBP1_TS2
),
PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE P2_TS
(
SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE SUBP2_TS1,
SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE SUBP2_TS2
)
)
示例2：使用TEMPLATE模板
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 SUB1 VALUES ('ACTIVE') TABLESPACE SUBP1_TS1,
SUBPARTITION SUB2 VALUES ('INACTIVE') TABLESPACE SUBP2_TS2
)
(
PARTITION P1 VALUES LESS THAN (TO_DATE('2003-01-01','YYYY-MM-DD')) TABLESPACE P1_TS,
PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE P2_TS
)
5、复合范围散列分区：这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区。与上面的定义方式非常的类似，在此不单独举例。

 

表分区对于用户来说是透明的，在插入数据时Oracle会自动判断插入的数据，然后放入相应的表分区中。但有时想单独查询某个分区中的数据时，就必须手工指定分区的名称。
示例1：(此示例基于：4、复合范围列表分区的示例一)

INSERT INTO SALES VALUES('00001','01-1月-02',100,'ACTIVE')
INSERT INTO SALES VALUES('00002','01-1月-01',200,'ACTIVE')
INSERT INTO SALES VALUES('00003','01-2月-03',300,'INACTIVE')
INSERT INTO SALES VALUES('00004','04-2月-03',300,'INACTIVE')
INSERT INTO SALES VALUES('00005','04-2月-02',300,'INACTIVE')