Oracle 11g 中的分区表

最新推荐文章于 2022-04-18 16:11:07 发布

cuanjianxiao3267

最新推荐文章于 2022-04-18 16:11:07 发布

阅读量124

点赞数

文章标签：数据库

1、 11g 中的分区表新特性

Partition（分区）一直是 Oracle 数据库引以为傲的一项技术，正是分区的存在让 Oracle 高效的处理海量数据成为可能，在 Oracle 11g 中，分区技术在易用性和可扩展性上再次得到了增强。

1.1 Interval Partitioning

在曾经的一个项目中，由于数据量的巨大，所以表设计为每一个小时一个分区，数据库管理员日常要做的一件重复而无聊的工作就是每隔一天要生成新的 24 个分区，用以存储第二天的数据。而在 11g 中这项工作可以交由 Oracle 自动完成了，基于 Range 和 List 的 Interval Partitioning 分区类型登场。

CREATE TABLE TB_INTERVAL

PARTITION BY RANGE (time_col)

INTERVAL(NUMTOYMINTERVAL(1, 'month'))

(PARTITION P0 VALUES LESS THAN (TO_DATE('1-1-2010', 'dd-mm-yyyy')));

指定需要 Oracle 自动创建分区的间隔时间，上面这个例子是 1 个月，然后至少创建一个基本分区，上面这个例子是在 2010-1-1 之前的所有数据都在 P0 分区中，以后每个月的数据都会存放在 Oracle 自动创建的一个新分区中。

1.2 System Partitioning

系统分区，在这个新的类型中，我们不需要指定任何分区键，数据会进入哪个分区完全由应用程序决定，实际上也就是由 SQL 来决定，终于，我们在 Insert 语句中可以指定插入哪个分区了。

假设我们创建了下面这张分区表，注意，没有指定任何分区键：

CREATE TABLE systab (c1 integer, c2 integer) PARTITION BY SYSTEM

(

PARTITION p1 TABLESPACE tbs_1,

PARTITION p2 TABLESPACE tbs_2,

PARTITION p3 TABLESPACE tbs_3,

PARTITION p4 TABLESPACE tbs_4 );

现在由 SQL 语句来指定插入哪个分区：

-- 数据插入 p1 分区

INSERT INTO systab PARTITION (p1) VALUES (4,5);

-- 数据插入第 2 个分区，也就是 p2 分区

INSERT INTO systab PARTITION (2) VALUES (7,8);

-- 为了实现绑定变量，用 pno 变量来代替实际分区号，以避免过度解析

INSERT INTO systab PARTITION (:pno) VALUES (9,10);

由于 System Partitioning 的特殊性，所以很明显，这种类型的分区将不支持

Partition Split 操作，也不支持 create table as select 操作。

1.3 More Composite Partitioning

在 10g 中，我们知道复合分区只支持 Range-List 和 Range-Hash，而在 11g 中复合分区的类型大大增加，现在 Range，List，Interval 都可以作为 Top level 分区，而 Second level 则可以是 Range，List，Hash，也就是在 11g 中可以有 3*3=9种复合分区，满足更多的业务需求。

1.4 Virtual Column-Based Partitioning

Virtual Column 是 11g 中的一个新功能，这种列中的数据并不实际存储于磁盘上（我们可以看成是一个类似 Function 的列），只有当读取的时候才实时计算。暂时不讨论性能问题，这个功能还是比较有意思的。

可以通过这样的语句来创建虚拟列。

CREATE TABLE tb_v (

col_1 number(6) not null,

col_2 number not null,

col_v as (col_1 *( 1+col_2)

);

虚拟列虽然没有实际的存储空间，但是却可以跟其他普通列一样，创建索引，作为分区键，甚至可以收集统计信息。

1.5 11g 虚拟列实现按星期分区表

在 11g 之前分区表的 partition key 必须是物理存在的。 11g 开始提供了虚拟列，并且可以作为 partition key 。

1. 创建分区表：

CREATE TABLE PT

(

getdate date NOT NULL,

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)
);

2. 插入测试数据

SQL> insert into pt(getdate) values(sysdate);

已创建 1 行。

SQL> insert into pt(getdate) values(sysdate-1);

已创建 1 行。

SQL> insert into pt(getdate) values(sysdate-2);

已创建 1 行。

SQL> insert into pt(getdate) values(sysdate-3);

已创建 1 行。

SQL> insert into pt(getdate) values(sysdate-4);

已创建 1 行。

SQL> insert into pt(getdate) values(sysdate-5);

已创建 1 行。

SQL> insert into pt(getdate) values(sysdate-6);

已创建 1 行。

SQL> insert into pt(getdate) values(sysdate-7);

已创建 1 行。

SQL> commit;

提交完成。

3. 查看数据：

SQL> select * from pt;
GETDATE	WD
-------------- ---------
06-6 月 -10	1
07-6 月 -10	2
08-6 月 -10	3
09-6 月 -10	4
10-6 月 -10	5
03-6 月 -10	5
04-6 月 -10	6
05-6 月 -10	7

已选择 8 行。

SQL> alter session set nls_date_format='YYYY-MM-DD';

会话已更改。
SQL> select * from pt;
GETDATE	WD
---------- ----------
2010-06-06	1
2010-06-07	2
2010-06-08	3
2010-06-09	4
2010-06-10	5
2010-06-03	5
2010-06-04	6
2010-06-05	7
已选择 8 行。

4. 查看每个分区里的内容：

SQL> select * from pt partition(sun);

GETDATE WD

---------- ----------

2010-06-06 1

SQL> select * from pt partition(mon);

GETDATE WD

---------- ----------

2010-06-07 2

SQL> select * from pt partition(tue);

GETDATE WD

---------- ----------

2010-06-08 3

SQL> select * from pt partition(wed);

GETDATE WD

---------- ----------

2010-06-09 4

SQL> select * from pt partition(fri);

GETDATE WD

---------- ----------

2010-06-04 6

SQL> select * from pt partition(sat);

GETDATE WD

---------- ----------

2010-06-05 7

SQL>

注：如果是 10g，就要添加一物理列。

1.5.1 Interval 分区示例

在 Oracle Database 11g 中还可以创建新类型的Interval 分区表，Interval 类型分区表，可以根据加载数据，自动创建指定间隔的分区。

1.5.1.1 创建按月分区的分区表

1. 创建分区表

/* Formatted on 2010/6/10 20:21:12 (QP5 v5.115.810.9015) */

create table intervalpart (c1 number, c3 date)

partition by range (c3)

interval ( numtoyminterval (1, 'month') )

(partition part1

values less than (to_date ('01/12/2010', 'mm/dd/yyyy')),

partition part2

values less than (to_date ('02/12/2010', 'mm/dd/yyyy'))

)

注意：如果在建 Interval 分区表是没有把所有的分区写完成，在插入相关数据后会自动生成分区

2. 查看现在表的分区：

SQL> select table_name,partition_name from user_tab_partitions where

table_name='INTERVALPART';

TABLE_NAME PARTITION_NAME

------------------------------ ------------------------------

INTERVALPART PART1

INTERVALPART PART2

3. 插入测试数据：

SQL> begin

2 for i in 0 .. 11 loop

3 insert into intervalpart values(i,add_months(to_date('2010-1-1','yyyy-mm-dd'),i));

4 end loop ;

5 commit;

6 end;

7 /

PL/SQL 过程已成功完成。

补充：add_months() 函数获取前一个月或者下一个月的月份，参数中负数代表往前，正数代表往后。

--上一个月

select to_char(add_months(trunc(sysdate),-1),'yyyymm') from dual;

--下一个月

select to_char(add_months(trunc(sysdate),1),'yyyymm') from dual;

4. 观察自动创建的分区：

SQL>	select	table_name,partition_name from user_tab_partitions where
table_name='INTERVALPART';
TABLE_NAME		PARTITION_NAME
------------------------------ ------------------------------
INTERVALPART		PART1
INTERVALPART		PART2
INTERVALPART		SYS_P22
INTERVALPART		SYS_P23
INTERVALPART		SYS_P24
INTERVALPART		SYS_P25
INTERVALPART		SYS_P26
INTERVALPART		SYS_P27
INTERVALPART		SYS_P28
INTERVALPART		SYS_P29
INTERVALPART		SYS_P30
INTERVALPART		SYS_P31