oracle分區表使用

分區概述：

為了簡化資料庫大表的管理，從ORACLE8推出了分區選項。分區将表分離在若幹不同的表空間上，用分而治之的方法來支撐無限膨脹的大表，給大表在實體一級的可管理性。将大表分割成較小的分區可以改善表的維護、備份、恢複、事務及查詢性能。針對目前社保及電信行業的大量日常業務資料，可以推薦使用ORACLE8的該選項。

分區優點：

1 、增強可用性：如果表的一個分區由于系統故障而不能使用，表的其餘好的分區仍然可以使用；

2 、減少關閉時間：如果系統故障隻影響表的一部分分區，那麼隻有這部分分區需要修複，故能比整個大表修複花的時間更少；

3 、維護輕松：如果需要重建表，獨立管理每個分區比管理單個大表要輕松得多；

4 、均衡I/O:可以把表的不同分區配置設定到不同的磁盤來平衡I/O改善性能；

5 、改善性能：對大表的查詢、增加、修改等操作可以分解到表的不同分區來并行執行，可使運作速度更快；

6 、分區對使用者透明，最終使用者感覺不到分區的存在。

關于何時應該進行分區,Oracle有如下建議：

（1）Tables greater than 2GB should always be considered for partitioning. 表大小超過2G，總是要考慮分區

（2）Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only. 表有曆史資料和目前資料之分

一、常見分區：

1、範圍分區

建立表空間，供分區使用

create tablespace ts_emp1 datafile 'D:\APP\PETER\ORADATA\WJIAO\ts_emp1.dbf' size 1m;

create tablespace ts_emp2 datafile 'D:\APP\PETER\ORADATA\WJIAO\ts_emp2.dbf' size 1m;

create tablespace ts_emp3 datafile 'D:\APP\PETER\ORADATA\WJIAO\ts_emp3.dbf' size 1m;

建立分區表

create table emp

(

empno number(4) not null,

ename varchar2(10),

sal number(7,2)

)

partition by range(empno)

(

partition p1000 values less than (1000) tablespace ts_emp1,

partition p2000 values less than (2000) tablespace ts_emp2,

partition pmax values less than (maxvalue) tablespace ts_emp3

);

檢視表内分區資訊

[email protected]>select segment_name,segment_type,partition_name from user_segments where segment_name='EMP';

SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME

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

EMP TABLE PARTITION P1000

EMP TABLE PARTITION P2000

EMP TABLE PARTITION PMAX

模拟資料

insert into emp values (100,'Tom',1000);

insert into emp values (500,'Peter',2000);

insert into emp values (1000,'Scott',3000);

insert into emp values (1999,'Bill',4000);

insert into emp values (5000,'Gates',6000);

commit;

檢視記錄分布

[email protected]>select * from emp;

EMPNO ENAME SAL

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

100 Tom 1000

500 Peter 2000

1000 Scott 3000

1999 Bill 4000

5000 Gates 6000

[email protected]>select * from emp partition(p1000);

EMPNO ENAME SAL

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

100 Tom 1000

500 Peter 2000

[email protected]>select * from emp partition(p2000);

EMPNO ENAME SAL

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

1000 Scott 3000

1999 Bill 4000

[email protected]>select * from emp partition(pmax);

EMPNO ENAME SAL

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

5000 Gates 6000

維護分區

exp導出指定表分區

C:\>exp userid=hr/hr file=d:\app\peter\oradata\emp_p1000.dmp tables=emp:p1000

imp導入指定表分區

C:\>imp userid=hr/hr file=d:\app\peter\oradata\emp_p1000.dmp tables=(emp:p1000) ignore=y 分區不存在，則插入到p2000

ignore=y必須要有，忽略建立錯誤，否則導入失敗

truncate指定分區

alter table emp truncate partition pmax;

drop指定分區

alter table emp drop partition pmax;

超出範圍的資料，報錯

[email protected]>insert into emp values (5000,'Gates',6000);

insert into emp values (5000,'Gates',6000)

ERROR at line 1:

ORA-14400: inserted partition key does not map to any partition

加入分區

alter table emp add partition pmax values less than(maxvalue);

[email protected]>insert into emp values (5000,'Gates',6000);

1 row created.

2、hash分區

hash分區最主要的機制是根據hash算法來計算具體某條紀錄應該插入到哪個分區中

（問：hash算法是幹什麼的？呵呵，隻能去看看資料結構了）

hash算法中最重要的是hash函數，Oracle中如果你要使用hash分區，隻需指定分區的數量即可

建議分區的數量采用2的n次方，這樣可以使得各個分區間資料分布更加均勻

hash列上數值不能有太多的重複值，否則會導緻資料分布不均勻

drop table emp purge;

create table emp

(

empno number(5) not null,

ename varchar2(10),

sal number(7,2)

)

partition by hash(empno)

partitions 8 store in (users)

;

--插入資料，遞增序列，分區資料分布均勻

set timing on;

begin

for i in 1..96400 loop

execute immediate 'insert into emp values(:1,:2,:3)' using i,'emp'||i,i;

end loop;

commit;

end;

[email protected]>analyze table emp compute statistics for table for all columns for all indexes;

[email protected]>select partition_name,count(*)

2 from user_extents

3 where segment_name = 'EMP'

4 group by partition_name;

PARTITION_NAME COUNT(*)

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

SYS_P82 6

SYS_P79 6

SYS_P81 6

SYS_P77 6

SYS_P78 6

SYS_P80 6

SYS_P83 6

SYS_P84 6

8 rows selected.

hash列上重複值過多，導緻資料分布不均勻，建分區表應盡量使資料均勻分布

set timing on;

begin

for i in 1..96400 loop

execute immediate 'insert into emp values(:1,:2,:3)' using mod(i,4),'emp'||i,i;

end loop;

commit;

end;

[email protected]>select partition_name,count(*)

2 from user_extents

3 where segment_name = 'EMP'

4 group by partition_name;

PARTITION_NAME COUNT(*)

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

SYS_P205 6

SYS_P206 6

SYS_P208 16

SYS_P210 15

SYS_P212 16

SYS_P209 6

SYS_P207 6

SYS_P211 16

8 rows selected.

3、複合分區

将範圍分區、hash分區綜合在一起使用的技術

drop table emp purge;

create table emp

(

empno number(5) not null,

ename varchar2(10),

sal number(7,2)

)

partition by range(empno)

subpartition by hash(empno)

subpartitions 2

(

partition p1000 values less than (1000),

partition p2000 values less than (2000),

partition pmax values less than (maxvalue)

);

上面的例子中将雇員表先按照雇員工号empno進行範圍分區，然後再把每個分區分為兩個子hash分區。例子中一共将産生6個分區

[email protected]>select segment_name,segment_type,partition_name from user_segments where segment_name='EMP';

SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME

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

EMP TABLE SUBPARTITION SYS_SUBP219

EMP TABLE SUBPARTITION SYS_SUBP220

EMP TABLE SUBPARTITION SYS_SUBP221

EMP TABLE SUBPARTITION SYS_SUBP222

EMP TABLE SUBPARTITION SYS_SUBP223

EMP TABLE SUBPARTITION SYS_SUBP224

6 rows selected.

查詢需要使用子分區

[email protected]>select * from emp subpartition(SYS_SUBP219);

EMPNO ENAME SAL

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

100 Tom 1000

500 Peter 2000

4、清單分區

一般分區列包含資料可枚舉，有限個值，例如地區、或國家等

create table emp

(

empno number(4) not null,

ename varchar2(10),

location varchar2(13)

)

partition by list(location)

(

partition pnkg values ('Nanjing'),

partition pswc values ('Suzhou','Wuxi','Changzhou'),

partition pxls values ('Xuzhou','Lianyg','Suqian')

);

模拟資料

insert into emp values (100,'Tom','Nanjing');

insert into emp values (500,'Peter','Changzhou');

insert into emp values (1000,'Scott','Xuzhou');

insert into emp values (1999,'Bill','Wuxi');

insert into emp values (5000,'Gates','Lianyg');

commit;

檢視記錄分布

[email protected]>select * from emp partition(pnkg);

EMPNO ENAME LOCATION

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

100 Tom Nanjing

[email protected]>select * from emp partition(pswc);

EMPNO ENAME LOCATION

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

500 Peter Changzhou

1999 Bill Wuxi

二、如果一個表建立時沒有利用分區技術，如何對一個沒有添加過分區表的表上添加分區表呢？

比如scott下已存在emp_unpart表，未使用表分區技術

1、最簡單方法

drop table emp_part purge;

create table emp_part

partition by hash(empno)

partitions 4 store in (users)

as select * from emp_unpart;

這個方法雖然簡單，由于實際生産環境中資料量太大，幾乎不可行

2、線上重定義，利用oracle提供的dbms_redefinition包

生成相同表結構的分區表

drop table emp_part purge;

create table emp_part

partition by hash(empno)

partitions 4 store in (users)

as select * from emp_unpart where 1=2;

[email protected]>select segment_name,partition_name from user_extents;

SEGMENT_NAME PARTITION_NAME

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

EMP_PART SYS_P285

EMP_PART SYS_P286

EMP_PART SYS_P287

EMP_PART SYS_P288

為了能夠看到表的變化

[email protected]>grant select on emp_unpart to hr;

[email protected]>select tablespace_name,table_name,owner from all_tables where owner = 'SCOTT';

TABLESPACE_NAME TABLE_NAME OWNER

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

USERS EMP_UNPART SCOTT

sys下執行表的線上重定義

[email protected]>begin --使用包 DBMS_REDEFINITION 将非分區表轉為分區表

2 dbms_redefinition.can_redef_table('SCOTT','EMP_UNPART',2);

3 dbms_redefinition.start_redef_table('SCOTT','EMP_UNPART','EMP_PART',null,2);

4 dbms_redefinition.finish_redef_table('SCOTT','EMP_UNPART','EMP_PART');

5 end;

6 /

PL/SQL procedure successfully completed.

[email protected]>select segment_name,partition_name from user_extents;

SEGMENT_NAME PARTITION_NAME

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

EMP_UNPART SYS_P285

EMP_UNPART SYS_P286

EMP_UNPART SYS_P287

EMP_UNPART SYS_P288

[email protected]>select tablespace_name,table_name,owner from all_tables where owner = 'SCOTT';

TABLESPACE_NAME TABLE_NAME OWNER

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

USERS EMP_PART SCOTT

從資料可以看出：線上重定義後，未分區的表重新使用分區表的定義；

三、如何實作分區滑動視窗

有需求：由于資料量巨大，隻計劃保留最近3個月資料，之前的資料入資料倉庫

drop table emp purge;

drop table t_emp purge;

create table emp

(

empno number(4) not null,

ename varchar2(10),

hiredate date

)

partition by range(hiredate)

(

partition p201309 values less than (to_date('20131001','yyyymmdd')),

partition p201310 values less than (to_date('20131101','yyyymmdd')),

partition p201311 values less than (to_date('20131201','yyyymmdd')),

partition pmax values less than (maxvalue)

);

模拟資料

insert into emp values (100,'Tom', to_date('20130907','yyyymmdd'));

insert into emp values (500,'Peter', to_date('20131007','yyyymmdd'));

insert into emp values (1000,'Scott',to_date('20131107','yyyymmdd'));

insert into emp values (1999,'Bill', to_date('20131207','yyyymmdd'));

insert into emp values (5000,'Gates',to_date('20140107','yyyymmdd'));

commit;

假設目前時間為20131231235959，1s後操作如下

1、exchange分區和臨時表，發生資料交換

建立另一張相同表結構的表

create table t_emp as select * from emp where 1=2;

檢視記錄分布

[email protected]>select * from emp partition(p201309);

EMPNO ENAME HIREDATE

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

100 Tom 07-SEP-13

[email protected]>select * from t_emp;

no rows selected

利用exchange實作資料交換。exchange利用了指針原理，僅僅改變資料字典，速度非常快，幾乎不涉及IO操作

[email protected]>alter table emp exchange partition p201309 with table t_emp;

注意exchange後資料變化，表面上分區和表資料發生了交換，實際是背景指針改變連結位置

[email protected]>select * from emp partition(p201309);

no rows selected

[email protected]>select * from t_emp;

EMPNO ENAME HIREDATE

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

100 Tom 07-SEP-13

2、merge表分區

[email protected]>alter table emp merge partitions p201309,p201310 into partition p201310;

檢視資料字典變化

[email protected]>select partition_name from user_extents where segment_name = 'EMP';

PARTITION_NAME

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

P201310

P201311

PMAX

3、split最後一個分區

[email protected]>alter table emp split partition pmax at (to_date('20140101','yyyymmdd')) into (partition p201312 tablespace ts_emp2, partition pmax tablespace ts_emp3);

檢視資料字典變化

[email protected]>select partition_name from user_extents where segment_name = 'EMP';

PARTITION_NAME

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

P201310

P201311

P201312

PMAX

檢視記錄分布

[email protected]>select * from emp partition(p201312);

EMPNO ENAME HIREDATE

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

1999 Bill 07-DEC-13

[email protected]>select * from emp partition(pmax);

EMPNO ENAME HIREDATE

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

5000 Gates 07-JAN-14

接下來将t_emp的資料入到資料倉庫

至此，滑動視窗完成

oracle分區表使用

繼續閱讀

壓測--部署[email protected] - PerfMon Metrics Collector

Jmeter監聽器圖形結果插件

ORACLE 表壓縮

oracle中的start with connect by用法

使用JDeveloper Remote debug PLSQL程式

LoadRunner 的多種Vuser類型

LoadRunner(5)_LoadRunner工作過程

性能測試（并發負載壓力）測試分析－簡要篇

賦予使用者dba權限--解釋以上：

Oracle的基本操作

為什麼要選擇UniDAC

windows不能在本地計算機上運作oracleDbConsoleorcl

雲計算面試題——檔案/權限/分區/軟體包管理

SQL語言基礎：常用的資料查詢語句

多線程擷取随機數性能對比（C vs C++）

Oracle 批量查詢傳入List 傳回List