程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> Oracle 性能優化――創建分區表

Oracle 性能優化――創建分區表

編輯: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行。

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

7566 JONES MANAGER 7839 02-4月 -81 2975

插入的記錄中,sal<3000,deptno=20,根據分析,此記錄是存儲在表空間test02中,位於sal_p2分區中p2子分區,查詢結果正是如此。

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved