分區表類型(oracle分區表可以分為幾類)

Oracle分區表分為四類:范圍分區表;列表分區表;哈希分區表;組合分區表

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 hh34:mi:ss')) TABLESPACE arsystem,

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

PARTITION ORD_ACT_PART03 VALUES LESS THAN (TO_DATE('2015-09-01 00:00:00','yyyy-mm-dd hh34: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 hh34:mi:ss')),

partition part_02 values less than(TO_DATE('2015-08-01 00:00:00','yyyy-mm-dd hh34: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 hh34:mi:ss')) TABLESPACE arsystem,

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

PARTITION ORD_ACT_PART03 VALUES LESS THAN (TO_DATE('2015-09-01 00:00:00','yyyy-mm-dd hh34: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);

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

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_numas (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分區表可以分為幾類”的內容了,經過本文的學習后,相信大家對oracle分區表可以分為幾類這一問題有了更深刻的體會,具體使用情況還需要大家實踐驗證。這里是本站,小編將為大家推送更多相關知識點的文章,歡迎關注!