oracle分區(qū)表可以分為幾類

這篇文章主要講解了“oracle分區(qū)表可以分為幾類”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“oracle分區(qū)表可以分為幾類”吧！

Oracle分區(qū)表分為四類：范圍分區(qū)表；列表分區(qū)表；哈希分區(qū)表；組合分區(qū)表

1.列表分區(qū)表(list)

CREATE TABLE list_example(

dname VARCHAR2(10),

DATA VARCHAR2(20)

)

PARTITION BY LIST(dname)

(

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

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

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

PARTITION part04 VALUES('限制登記')

);

2.范圍分區(qū)

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

);

時(shí)間分區(qū)

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

 );

哈希分區(qū)

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分區(qū)最主要的機(jī)制是根據(jù)hash算法來計(jì)算具體某條紀(jì)錄應(yīng)該插入到哪個(gè)分區(qū)中,hash算法中最重要的是hash函數(shù)，Oracle中如果你要使用hash分區(qū)，只需指定分區(qū)的數(shù)量即可。建議分區(qū)的數(shù)量采用2的n次方，這樣可以使得各個(gè)分區(qū)間數(shù)據(jù)分布更加均勻。

組合分區(qū)

范圍-散列分區(qū)

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

    )

    );

范圍-哈希分區(qū)

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)

    );

在分區(qū)表上可以建立三種類型的索引：1和普通表一樣的全局索引；2.全局分區(qū)索引；3.本地分區(qū)索引

1.建立普通的索引

create index com_index_range_example_id on range_example(id);

2.建立本地分區(qū)索引

create index  local_index_range_example_id on range_example(id) local;

3.建立全局分區(qū)索引

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)

);

對(duì)于分區(qū)索引的刪除，local index 不能指定分區(qū)名稱，單獨(dú)的刪除分區(qū)索引。

local index 對(duì)應(yīng)的分區(qū)會(huì)伴隨著data分區(qū)的刪除而一起被刪除。global partition index 可以指定分區(qū)名稱，刪除某一分區(qū)。但是有一點(diǎn)要注意，如果該分區(qū)不為空，則會(huì)導(dǎo)致更高一級(jí)的索引分區(qū)被置為UNUSABLE 。

ALTER INDEX gidx_range_exampel_id drop partition part_01 ; 此句將導(dǎo)致part_02 狀態(tài)為UNUSABLE

分區(qū)表的操作

1.查詢

select * from part_date partition(ORD_ACT_PART01);

2.刪除某個(gè)分區(qū)

alter table part_date drop partition ORD_ACT_PART04;

alter table part_date truncate partition ORD_ACT_PART04;

3.添加分區(qū)

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

4.創(chuàng)建索引

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)

);

創(chuàng)建分區(qū)表

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

);

插入測試數(shù)據(jù)

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');

在線重定義過程

測試該表是否可分區(qū)

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

開始

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

--插入測試數(shù)據(jù)（可選）

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

同步數(shù)據(jù)

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

完成

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

將分區(qū)表重命名

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)

 );

分區(qū)表

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

);

插入數(shù)據(jù)

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: 不能聯(lián)機(jī)重新定義無主鍵的表 "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;

查看分區(qū)時(shí)的錯(cuò)誤

select * from DBA_REDEFINITION_ERRORS;

虛擬列分區(qū)舉例

要求按月進(jìn)行分區(qū)，并且這12個(gè)分區(qū)可以循環(huán)使用，只保留2個(gè)月的數(shù)據(jù)，所以使用11g的虛擬列進(jìn)行分區(qū)

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分區(qū)表可以分為幾類”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對(duì)oracle分區(qū)表可以分為幾類這一問題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！