oracle分区表创建

oracle创建分区表：
1、准备工作
创建相应的表空间，使各分区放于不同的表空间上：
SQL> conn / as sysdba
已连接。
SQL> create tablespace test01 datafile 'D:xtdbtest01.dbf' size 50m;

表空间已创建。

SQL> create tablespace test02 datafile 'D:xtdbtest02.dbf' size 50m;

表空间已创建。

SQL> create tablespace test03 datafile 'D:xtdbtest03.dbf' size 50m;

表空间已创建。

SQL> create tablespace test04 datafile 'D:xtdbtest04.dbf' size 100m;

表空间已创建。

为了进行对比，在表空间test04上创建一个未分区的表test_emp01，其结构与scott.emp一样：
SQL> conn scott/tiger
已连接。
SQL> CREATE TABLE test_emp01
2 ( "EMPNO" NUMBER(4,0) not null,
3 "ENAME" VARCHAR2(10),
4 "JOB" VARCHAR2(9),
5 "MGR" NUMBER(4,0),
6 "HIREDATE" DATE,
7 "SAL" NUMBER(7,2),
8 "COMM" NUMBER(7,2),
9 "DEPTNO" NUMBER(2,0))
10 TABLESPACE test04
11 /

表已创建。
2、创建范围分区表
根据表中某个值的范围进行分区，根据某个值的范围，决定将该条数据放在哪个分区。通过sal取值范围创建范围分区：
SQL> CREATE TABLE test01
2 ( "EMPNO" NUMBER(4,0) not null,
3 "ENAME" VARCHAR2(10),
4 "JOB" VARCHAR2(9),
5 "MGR" NUMBER(4,0),
6 "HIREDATE" DATE,
7 "SAL" NUMBER(7,2),
8 "COMM" NUMBER(7,2),
9 "DEPTNO" NUMBER(2,0))
10 partition by range(sal)
11 (partition sal_p1 values less than (1500) tablespace test01,
12 partition sal_p2 values less than (3000) tablespace test02,
13 partition sal_p3 values less than (maxvalue) tablespace test03);

表已创建。
往test01中插入大量的数据：
SQL> insert into test01 select * from emp;

已创建14行。

SQL> insert into test01 select * from emp;

已创建14行。

已创建344064行。
SQL> commit;

提交完成。
为了对比，在表test_emp01中插入相同的数据量：
SQL> insert into test_emp01
2 select * from test01;

已创建688128行。

SQL> commit;

提交完成。
在表test_emp01中查询sal<1500的记录：
select * from test_emp01 where sal < 1500;
此时查看系统I/O
iostat 2 15
如果是windows系统，则可通过任务管理器或其他工具查看。
在表test01中查询分区1的记录：
select * from test01 partition(sal_p1);
查看系统I/O
iostat 2 15
如果是windows系统，则可通过任务管理器或其他工具查看。
通过对比两次I/O，可发现分区表可以很好的提高系统的I/O性能。
3、创建hash分区表
hash分区是通过分区键的hash值来均匀分布数据的一种分区类型，下例通过scott.emp的empno列值进行hash分区：
SQL> CREATE TABLE test02
2 ( "EMPNO" NUMBER(4,0) not null,
3 "ENAME" VARCHAR2(10),
4 "JOB" VARCHAR2(9),
5 "MGR" NUMBER(4,0),
6 "HIREDATE" DATE,
7 "SAL" NUMBER(7,2),
8 "COMM" NUMBER(7,2),
9 "DEPTNO" NUMBER(2,0))
10 partition by hash(empno)
11 (partition test02_p1 tablespace test01,
12 partition test02_p2 tablespace test02,
13 partition test02_p3 tablespace test03);

表已创建。
往表中插入数据
SQL> insert into test02 select * from emp;

已创建14行。

SQL> select count(*) from test02;

COUNT(*)
----------
14

SQL> insert into test02 select * from emp where sal < 3000;

已创建9行。

SQL> insert into test02 select * from emp where sal < 1500;

已创建6行。

SQL> commit;

提交完成。

SQL> select count(*) from test02;

COUNT(*)
----------
29
查看hash分区结果
SQL> select * from test02 partition(test02_p1);
SQL> select * from test02 partition(test02_p2);
SQL> select * from test02 partition(test02_p3);
从结果可以看出，oracle按empno将记录散列的插入三个分区中，即三个不同的表空间中。
4、创建列表分区
不能严格按照范围分区对表进行分区，也不希望hash分区那样由系统来进行散列的分配，需要自主控制数据自如的插入分区中，列表分区是最好的选择。下例按scott.emp中的deptno将表划分为3个分区。
SQL> CREATE TABLE test03
2 ( "EMPNO" NUMBER(4,0) not null,
3 "ENAME" VARCHAR2(10),
4 "JOB" VARCHAR2(9),
5 "MGR" NUMBER(4,0),
6 "HIREDATE" DATE,
7 "SAL" NUMBER(7,2),
8 "COMM" NUMBER(7,2),
9 "DEPTNO" NUMBER(2,0))
10 partition by list(deptno)
11 (partition test03_p1 values(10) tablespace test01,
12 partition test03_p2 values(20) tablespace test02,
13 partition test03_p3 values(30) tablespace test03);

表已创建。
向表中插入数据：
SQL> insert into test03 select * from emp;

已创建14行。

SQL> insert into test03 select * from emp where deptno=10;

已创建3行。

SQL> insert into test03 select * from emp where deptno=20;

已创建5行。

SQL> commit;

提交完成。

SQL> select count(*) from test03;

COUNT(*)
----------
22
查询列表分区的结果：
SQL> select * from test03 partition(test03_p1);
SQL> select * from test03 partition(test03_p2);
SQL> select * from test03 partition(test03_p3);
5、创建复合分区表
分为两种：A、先使用范围分区，然后在每个分区内再使用hash分区
B、先使用范围分区，然后在每个分区内再使用列表分区
6、第一种复合分区：
在scott.emp中按sal列的取值范围将表分为三个分区，然后再将每个分区按empno列值用hash分区的方法分为三个子分区。
SQL> CREATE TABLE test04
2 ( "EMPNO" NUMBER(4,0) not null,
3 "ENAME" VARCHAR2(10),
4 "JOB" VARCHAR2(9),
5 "MGR" NUMBER(4,0),
6 "HIREDATE" DATE,
7 "SAL" NUMBER(7,2),
8 "COMM" NUMBER(7,2),
9 "DEPTNO" NUMBER(2,0))
10 partition by range(sal)
11 subpartition by hash(empno)
12 subpartitions 3 store in(test01, test02, test03)
13 (partition sal_p1 values less than (1500),
14 partition sal_p2 values less than (3000),
15 partition sal_p3 values less than (maxvalue));

表已创建。
往表中插入数据
SQL> insert into test04 select * from emp;
SQL> insert into test04 select * from emp where deptno=10;

已创建3行。

SQL> insert into test04 select * from emp where sal < 3000;

已创建9行。

SQL> commit;

提交完成。

SQL> select count(*) from test04;

COUNT(*)
----------
26
查看复合分区表的状态：
SQL> col partition_name format a20
SQL> col subpartition_name format a20
SQL> col tablespace_name format a20
SQL> select tablespace_name, partition_name, subpartition_name
2 from user_tab_subpartitions
3 where table_name='TEST04'
4 /

TABLESPACE_NAME PARTITION_NAME SUBPARTITION_NAME
-------------------- -------------------- --------------------
TEST01 SAL_P1 SYS_SUBP61
TEST02 SAL_P1 SYS_SUBP62
TEST03 SAL_P1 SYS_SUBP63
TEST01 SAL_P2 SYS_SUBP64
TEST02 SAL_P2 SYS_SUBP65
TEST03 SAL_P2 SYS_SUBP66
TEST01 SAL_P3 SYS_SUBP67
TEST02 SAL_P3 SYS_SUBP68
TEST03 SAL_P3 SYS_SUBP69

已选择9行。
7、第二种复合分区：
在scott.emp中按sal列的取值范围将表分为三个分区，然后再将每个分区按deptno列值用列表分区的方法分为三个子分区。
SQL> CREATE TABLE test05
2 ( "EMPNO" NUMBER(4,0) not null,
3 "ENAME" VARCHAR2(10),
4 "JOB" VARCHAR2(9),
5 "MGR" NUMBER(4,0),
6 "HIREDATE" DATE,
7 "SAL" NUMBER(7,2),
8 "COMM" NUMBER(7,2),
9 "DEPTNO" NUMBER(2,0))
10 partition by range(sal)
11 subpartition by list(deptno)
12 subpartition template
13 (subpartition p1 values(10),
14 subpartition p2 values(20),
15 subpartition p3 values(30))
16 (partition sal_p1 values less than (1500) tablespace test01,
17 partition sal_p2 values less than (3000) tablespace test02,
18 partition sal_p3 values less than (maxvalue) tablespace test03);

表已创建。
向表中插入数据：
SQL> insert into test05 select * from emp;

已创建14行。

SQL> insert into test05 select * from emp where deptno = 10;

已创建3行。

SQL> insert into test05 select * from emp where sal < 3000;

已创建9行。

SQL> commit;

提交完成。
SQL> select count(*) from test05;

COUNT(*)
----------
26
查看复合分区表的状态：
SQL> edit
已写入 file afiedt.buf

1 select tablespace_name, partition_name, subpartition_name
2 from user_tab_subpartitions
3* where table_name='TEST05'
SQL> /

TABLESPACE_NAME PARTITION_NAME SUBPARTITION_NAME
-------------------- -------------------- --------------------
TEST01 SAL_P1 SAL_P1_P1
TEST01 SAL_P1 SAL_P1_P2
TEST01 SAL_P1 SAL_P1_P3
TEST02 SAL_P2 SAL_P2_P1
TEST02 SAL_P2 SAL_P2_P2
TEST02 SAL_P2 SAL_P2_P3
TEST03 SAL_P3 SAL_P3_P1
TEST03 SAL_P3 SAL_P3_P2
TEST03 SAL_P3 SAL_P3_P3

已选择9行。
SQL> select * from test05 subpartition(sal_p2_p2);

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-4月 -81 2975
20

7566 JONES MANAGER 7839 02-4月 -81 2975
20
插入的记录中，sal<3000，deptno=20，根据分析，此记录是存储在表空间test02中，位于sal_p2分区中p2子分区，查询结果正是如此。