1.列表分割槽表(list)

CREATE TABLE list_example(

dname VARCHAR2(10),

DATA VARCHAR2(20)

)

PARTITION BY LIST(dname)

(

PARTITION part01 VALUES('初始登記','轉移登記'),

PARTITION part02 VALUES('更名登記','樓盤變更'),

PARTITION part03 VALUES('抵押登記'),

PARTITION part04 VALUES('限制登記')

);

2.範圍分割槽

CREATE TABLE example

(

CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,

FIRST_NAME VARCHAR2(30) NOT NULL,

LAST_NAME VARCHAR2(30) NOT NULL,

PHONE VARCHAR2(15) NOT NULL,

EMAIL VARCHAR2(80),

STATUS CHAR(1)

)

PARTITION BY RANGE (CUSTOMER_ID)

(

PARTITION exam1 VALUES LESS THAN (100000) TABLESPACE arsystem,

PARTITION exam2 VALUES LESS THAN (200000) TABLESPACE arsystem,

PARTITION exam3 VALUES LESS THAN (300000) TABLESPACE arsystem

);

時間分割槽

CREATE TABLE part_date

(

ORDER_ID NUMBER(7) NOT NULL,

ORDER_DATE DATE,

TOTAL_AMOUNT NUMBER,

CUSTOTMER_ID NUMBER(7),

PAID CHAR(1)

)

PARTITION BY RANGE (ORDER_DATE)

(

PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('2015-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE arsystem,

PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('2015-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE arsystem,

PARTITION ORD_ACT_PART03 VALUES LESS THAN (TO_DATE('2015-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE arsystem,

PARTITION ORD_ACT_PART04 VALUES LESS THAN (MAXVALUE)TABLESPACE arsystem

);

雜湊分割槽

create table HASH_PART

(

transaction_id number primary key,

item_id number(8) not null

)

partition by hash(transaction_id)

(

partition part_01 tablespace ARSYSTEM,

partition part_02 tablespace ARSYSTEM,

partition part_03 tablespace ARSYSTEM

);

簡寫

CREATE TABLE emp_hash

(

empno NUMBER (4),

ename VARCHAR2 (30),

sal NUMBER

)

PARTITION BY HASH (empno) PARTITIONS 8

STORE IN (arsystem1,arsystem2);

hash分割槽最主要的機制是根據hash演算法來計算具體某條紀錄應該插入到哪個分割槽中,hash演算法中最重要的是hash函式，Oracle中如果你要使用hash分割槽，只需指定分割槽的數量即可。建議分割槽的數量採用2的n次方，這樣可以使得各個分割槽間資料分佈更加均勻。

組合分割槽

範圍-雜湊分割槽

CREATE TABLE SALES

(

PRODUCT_ID VARCHAR2(5),

SALES_DATE DATE,

SALES_COST NUMBER(10),

STATUS VARCHAR2(20)

)

PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)

(

PARTITION P1 VALUES LESS THAN(TO_DATE('2003-01-01','YYYY-MM-DD'))TABLESPACE ARSYSTEM

(

SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE ARSYSTEM,

SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE ARSYSTEM

),

PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE ARSYSTEM

(

SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE ARSYSTEM,

SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE ARSYSTEM

)

);

範圍-雜湊分割槽

create table dinya_test

(

transaction_id number primary key,

item_id number(8) not null,

item_description varchar2(300),

transaction_date date

)

partition by range(transaction_date)subpartition by hash(transaction_id) subpartitions 3 store in (arsystem,users)

(

partition part_01 values less than(TO_DATE('2015-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition part_02 values less than(TO_DATE('2015-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),

partition part_03 values less than(maxvalue)

);

在分割槽表上可以建立三種型別的索引：1和普通表一樣的全域性索引；2.全域性分割槽索引；3.本地分割槽索引

1.建立普通的索引

create index com_index_range_example_id on range_example(id);

2.建立本地分割槽索引

create index local_index_range_example_id on range_example(id) local;

3.建立全域性分割槽索引

create index gidx_range_exampel_id on range_example(id)

GLOBAL partition by range(id)

(

part_01 values less than(1000),

part_02 values less than(MAXVALUE)

);

對於分割槽索引的刪除，local index 不能指定分割槽名稱，單獨的刪除分割槽索引。

local index 對應的分割槽會伴隨著data分割槽的刪除而一起被刪除。global partition index 可以指定分割槽名稱，刪除某一分割槽。但是有一點要注意，如果該分割槽不為空，則會導致更高一級的索引分割槽被置為UNUSABLE 。

ALTER INDEX gidx_range_exampel_id drop partition part_01 ; 此句將導致part_02 狀態為UNUSABLE

分割槽表的操作

1.查詢

select * from part_date partition(ORD_ACT_PART01);

2.刪除某個分割槽

alter table part_date drop partition ORD_ACT_PART04;

alter table part_date truncate partition ORD_ACT_PART04;

3.新增分割槽

ALTER TABLE part_date ADD PARTITION ORD_ACT_PART04 VALUES LESS THAN(TO_DATE('2015-10-01','YYYY-MM-DD'));

4.建立索引

create index idx_part_date on part_date(order_id) local;

-----------------------線上重定義過程-----------------------

包中的定義，可以用pl/sql工具看下包dbms_redefinition的用法

-- Constants for the options_flag parameter of start_redef_table

cons_use_pk CONSTANT PLS_INTEGER := 1;---主鍵重定義

cons_use_rowid CONSTANT PLS_INTEGER := 2;---rowid重定義

增快處理速度，可新增並行

alter session force parallel dml parallel 4;

alter session force parallel query parallel 4;

建立普通表

CREATE TABLE putong_table

(

CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,

FIRST_NAME VARCHAR2(30) NOT NULL,

LAST_NAME VARCHAR2(30) NOT NULL,

PHONE VARCHAR2(15) NOT NULL,

EMAIL VARCHAR2(80),

STATUS CHAR(1)

);

建立分割槽表

CREATE TABLE part_table

(

CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,

FIRST_NAME VARCHAR2(30) NOT NULL,

LAST_NAME VARCHAR2(30) NOT NULL,

PHONE VARCHAR2(15) NOT NULL,

EMAIL VARCHAR2(80),

STATUS CHAR(1)

)

PARTITION BY RANGE (CUSTOMER_ID)

(

PARTITION exam1 VALUES LESS THAN (100000) TABLESPACE arsystem,

PARTITION exam2 VALUES LESS THAN (200000) TABLESPACE arsystem,

PARTITION exam3 VALUES LESS THAN (300000) TABLESPACE arsystem

);

插入測試資料

insert into putong_table values(1,'43t','f4y','t54','th','3');

insert into putong_table values(2,'43t','f4y','t54','th','2');

insert into putong_table values(3,'43t','f4y','t54','th','1');

線上重定義過程

測試該表是否可分割槽

exec dbms_redefinition.can_redef_table('ARADMIN', 'putong_table');

開始

EXEC DBMS_REDEFINITION.START_REDEF_TABLE('ARADMIN', 'putong_table', 'part_table');

--插入測試資料（可選）

insert into putong_table values(4,'43t','f4y','t54','th','4');

同步資料

EXEC dbms_redefinition.sync_interim_table('ARADMIN', 'putong_table', 'part_table');

完成

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('ARADMIN', 'putong_table', 'part_table');

將分割槽表重新命名

alter table part_table rename to part_table_new;

1.無主鍵表

普通表

CREATE TABLE date_putong

(

ORDER_ID NUMBER(7) NOT NULL,

ORDER_DATE DATE,

TOTAL_AMOUNT NUMBER,

CUSTOTMER_ID NUMBER(7),

PAID CHAR(1)

);

分割槽表

CREATE TABLE date_partition

(

ORDER_ID NUMBER(7) NOT NULL,

ORDER_DATE DATE,

TOTAL_AMOUNT NUMBER,

CUSTOTMER_ID NUMBER(7),

PAID CHAR(1)

)

PARTITION BY RANGE (ORDER_DATE)

(

PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('2015-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE arsystem,

PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('2015-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE arsystem,

PARTITION ORD_ACT_PART03 VALUES LESS THAN (TO_DATE('2015-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE arsystem,

PARTITION ORD_ACT_PART04 VALUES LESS THAN (MAXVALUE)TABLESPACE arsystem

);

插入資料

insert into date_putong values(1,TO_DATE('2015-08-24','yyyy-mm-dd'),1,2,3);

重定義過程

exec dbms_redefinition.can_redef_table('ARADMIN', 'date_putong',2);

EXEC DBMS_REDEFINITION.START_REDEF_TABLE('ARADMIN', 'date_putong', 'date_partition',null,2);

EXEC dbms_redefinition.sync_interim_table('ARADMIN', 'date_putong', 'date_partition');

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('ARADMIN', 'date_putong', 'date_partition');

--------------異常處理--------------

異常情況下終止操作

BEGIN

DBMS_REDEFINITION.ABORT_REDEF_TABLE(uname => 'ARADMIN',

orig_table => 'date_putong',

int_table => 'date_partition'

);

END;

或

DBMS_REDEFINITION.ABORT_REDEF_TABLE('ARADMIN', 'date_putong', 'date_partition');

ORA-12089: 不能聯機重新定義無主鍵的表 "ARADMIN"."DATE_PUTONG"

ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 56

ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 1498

ORA-06512: 在 line 2

alter table PUTONG_TABLE add primary key (CUSTOMER_ID)

ORA-12091

刪除物化檢視日誌

drop materialized view log on BS_T_WF_DEALPROCESS_BAK;

檢視分割槽時的錯誤

select * from DBA_REDEFINITION_ERRORS;

虛擬列分割槽舉例

要求按月進行分割槽，並且這12個分割槽可以迴圈使用，只保留2個月的資料，所以使用11g的虛擬列進行分割槽

create table MACHINE_TEMP_part

(

id NUMBER,

username VARCHAR2(30),

tcp VARCHAR2(30),

clientip VARCHAR2(30),

logintime VARCHAR2(30),

logouttime VARCHAR2(30),

serverip VARCHAR2(30),

insertdate DATE,

part_num as (to_nubmer(to_char(insertdate,'mm')))

)

tablespace UNIONMON

artition by range(partition_num)

(partition p1 values less than(2),

partition p2 values less than(3),

partition p3 values less than(4),

partition p4 values less than(5),

partition p5 values less than(6),

partition p6 values less than(7),

partition p7 values less than(8),

partition p8 values less than(9),

partition p9 values less than(10),

partition p10 values less than(11),

partition p11 values less than(12),

partition p12 values less than(13)

);

oracle分割槽表的分類及測試

相關文章