Oracle 性能优化——创建分区表

最新推荐文章于 2021-04-14 09:04:30 发布

cuidan1640

最新推荐文章于 2021-04-14 09:04:30 发布

阅读量174

点赞数

文章标签：数据库

Oracle 性能优化——创建分区表[@more@] 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行。

SQL> /

已创建14行。

SQL> insert into test01 select * from test01;

已创建42行。

SQL> /

已创建84行。

SQL> /

已创建168行。

SQL> /

已创建336行。

SQL> /

已创建672行。

SQL> /

已创建1344行。

SQL> /

已创建2688行。

SQL> /

已创建5376行。

SQL> /

已创建10752行。

SQL> /

已创建21504行。

SQL> /

已创建43008行。

SQL> /

已创建86016行。

SQL> /

已创建172032行。

SQL> /

已创建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(*)

----------

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(*)

----------

查看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(*)

----------

查询列表分区的结果：

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(*)

----------

查看复合分区表的状态：

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(*)

----------

查看复合分区表的状态：

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

插入的记录中，sal<3000，deptno=20，根据分析，此记录是存储在表空间test02中，位于sal_p2分区中p2子分区，查询结果正是如此。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9523925/viewspace-1032635/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9523925/viewspace-1032635/

cuidan1640

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle 性能优化——创建分区表

Oracle 性能优化——创建分区表[@more@]1、准备工作创建相应的表空间，使各分区放于不同的表空间上：SQL> conn / as sysdba已连接。SQL> create tablespace test0...
复制链接

扫一扫