oracle根據(jù)多字段創(chuàng)建分區(qū)表

最近有業(yè)務(wù)場景需要用多個字段做分區(qū)表，數(shù)據(jù)量比較大，保存時間也較長，經(jīng)過學(xué)習(xí)與實踐，算是基本完成，以下內(nèi)容為實踐樣例：

創(chuàng)新互聯(lián)公司堅持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：網(wǎng)站設(shè)計制作、網(wǎng)站設(shè)計、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時代的晉源網(wǎng)站設(shè)計、移動媒體設(shè)計的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！

---建表語句

create table t_table

(

areacode varchar2(10),

appdate date,

text varchar(10)

)

partition by range(appdate)--根據(jù)字段 appdate 創(chuàng)建主分區(qū)

interval(numtoyminterval(1,'MONTH')) --主分區(qū)按 月 自動創(chuàng)建分區(qū)

subpartition by list(areacode) --再按 地區(qū) 創(chuàng)建子分區(qū)

subpartition template( --指定明確的子分區(qū)信息

subpartition sub1 values('101'),

subpartition sub2 values('201'),

subpartition sub3 values('301')

)

(

partition mainpartition1 values less than(to_date('2019-04-01','yyyy-mm-dd'))--2019年4月1日前的放入mainpartition1分區(qū)，之后的自動分區(qū)

)

---模擬寫入測試數(shù)據(jù)

insert into t_table values('101',to_date('2019-03-03','yyyy-mm-dd'),'a');

insert into t_table values('101',to_date('2019-02-03','yyyy-mm-dd'),'a');

insert into t_table values('101',to_date('2019-04-03','yyyy-mm-dd'),'a');

insert into t_table values('201',to_date('2019-03-03','yyyy-mm-dd'),'a');

insert into t_table values('201',to_date('2019-05-03','yyyy-mm-dd'),'a');

insert into t_table values('301',to_date('2019-04-01','yyyy-mm-dd'),'a');

--查詢數(shù)據(jù)

select * from t_table;

--查詢主分區(qū)數(shù)據(jù)

select *from t_table partition (mainpartition1);

--查詢子分區(qū)數(shù)據(jù)

select *from t_table subpartition (mainpartition1_sub1);

--查看自動創(chuàng)建的主分區(qū)

select * from user_tab_partitions where table_name='T_TABLE'

Oracle數(shù)據(jù)庫分區(qū)表操作方法

在大型的企業(yè)應(yīng)用或企業(yè)級的數(shù)據(jù)庫應(yīng)用中 要處理的數(shù)據(jù)量通?？梢赃_(dá)到幾十到幾百GB 有的甚至可以到TB級 雖然存儲介質(zhì)和數(shù)據(jù)處理技術(shù)的發(fā)展也很快 但是仍然不能滿足用戶的需求 為了使用戶的大量的數(shù)據(jù)在讀寫操作和查詢中速度更快 Oracle提供了對表和索引進(jìn)行分區(qū)的技術(shù) 以改善大型應(yīng)用系統(tǒng)的性能

使用分區(qū)的優(yōu)點

·增強(qiáng)可用性 如果表的某個分區(qū)出現(xiàn)故障 表在其他分區(qū)的數(shù)據(jù)仍然可用

·維護(hù)方便 如果表的某個分區(qū)出現(xiàn)故障 需要修復(fù)數(shù)據(jù) 只修復(fù)該分區(qū)即可

·均衡I/O 可以把不同的分區(qū)映射到磁盤以平衡I/O 改善整個系統(tǒng)性能

·改善查詢性能 對分區(qū)對象的查詢可以僅搜索自己關(guān)心的分區(qū) 提高檢索速度

Oracle數(shù)據(jù)庫提供對表或索引的分區(qū)方法有三種

·范圍分區(qū)

·Hash分區(qū)（散列分區(qū)）

·復(fù)合分區(qū)

下面將以實例的方式分別對這三種分區(qū)方法來說明分區(qū)表的使用 為了測試方便 我們先建三個表空間

以下為引用的內(nèi)容

create tablespace dinya_space

datafile /test/demo/oracle/demodata/dinya dnf size M

create tablespace dinya_space

datafile /test/demo/oracle/demodata/dinya dnf size M

create tablespace dinya_space

datafile /test/demo/oracle/demodata/dinya dnf size M

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

范圍分區(qū)

范圍分區(qū)就是對數(shù)據(jù)表中的某個值的范圍進(jìn)行分區(qū) 根據(jù)某個值的范圍 決定將該數(shù)據(jù)存儲在哪個分區(qū)上 如根據(jù)序號分區(qū) 根據(jù)業(yè)務(wù)記錄的創(chuàng)建日期進(jìn)行分區(qū)等

需求描述 有一個物料交易表 表名 material_transactions 該表將來可能有千萬級的數(shù)據(jù)記錄數(shù) 要求在建該表的時候使用分區(qū)表 這時候我們可以使用序號分區(qū)三個區(qū) 每個區(qū)中預(yù)計存儲三千萬的數(shù)據(jù) 也可以使用日期分區(qū) 如每五年的數(shù)據(jù)存儲在一個分區(qū)上

根據(jù)交易記錄的序號分區(qū)建表 以下為引用的內(nèi)容

SQL create table dinya_test

(

transaction_id number primary key

item_id number( ) not null

item_description varchar ( )

transaction_date date not null

)

partition by range (transaction_id)

(

partition part_ values less than( ) tablespace dinya_space

partition part_ values less than( ) tablespace dinya_space

partition part_ values less than(maxvalue) tablespace dinya_space

);

Table created

建表成功 根據(jù)交易的序號 交易ID在三千萬以下的記錄將存儲在第一個表空間dinya_space 中 分區(qū)名為:par_ 在三千萬到六千萬之間的記錄存儲在第二個表空間

dinya_space 中 分區(qū)名為 par_ 而交易ID在六千萬以上的記錄存儲在第三個表空間dinya_space 中 分區(qū)名為par_

根據(jù)交易日期分區(qū)建表

以下為引用的內(nèi)容

SQL create table dinya_test

(

transaction_id number primary key

item_id number( ) not null

item_description varchar ( )

transaction_date date not null

)

partition by range (transaction_date)

(

partition part_ values less than(to_date( yyyy mm dd ))

tablespace dinya_space

partition part_ values less than(to_date( yyyy mm dd ))

tablespace dinya_space

partition part_ values less than(maxvalue) tablespace dinya_space

);

Table created

這樣我們就分別建了以交易序號和交易日期來分區(qū)的分區(qū)表 每次插入數(shù)據(jù)的時候 系統(tǒng)將根據(jù)指定的字段的值來自動將記錄存儲到制定的分區(qū)（表空間）中

當(dāng)然 我們還可以根據(jù)需求 使用兩個字段的范圍分布來分區(qū) 如partition

by range ( transaction_id transaction_date)

分區(qū)條件中的值也做相應(yīng)的改變 請讀者自行測試

Hash分區(qū)（散列分區(qū)）

散列分區(qū)為通過指定分區(qū)編號來均勻分布數(shù)據(jù)的一種分區(qū)類型 因為通過在I/O設(shè)備上進(jìn)行散列分區(qū) 使得這些分區(qū)大小一致 如將物料交易表的數(shù)據(jù)根據(jù)交易ID散列地存放在指定的三個表空間中

以下為引用的內(nèi)容

SQL create table dinya_test

(

transaction_id number primary key

item_id number( ) not null

item_description varchar ( )

transaction_date date

)

partition by hash(transaction_id)

(

partition part_ tablespace dinya_space

partition part_ tablespace dinya_space

partition part_ tablespace dinya_space

);

Table created

建表成功 此時插入數(shù)據(jù) 系統(tǒng)將按transaction_id將記錄散列地插入三個分區(qū)中 這里也就是三個不同的表空間中

復(fù)合分區(qū)

有時候我們需要根據(jù)范圍分區(qū)后 每個分區(qū)內(nèi)的數(shù)據(jù)再散列地分布在幾個表空間中 這樣我們就要使用復(fù)合分區(qū) 復(fù)合分區(qū)是先使用范圍分區(qū) 然后在每個分區(qū)內(nèi)再使用散列分區(qū)的一種分區(qū)方法 如將物料交易的記錄按時間分區(qū) 然后每個分區(qū)中的數(shù)據(jù)分三個子分區(qū) 將數(shù)據(jù)散列地存儲在三個指定的表空間中

以下為引用的內(nèi)容

SQL create table dinya_test

(

transaction_id number primary key

item_id number( ) not null

item_description varchar ( )

transaction_date date

)

partition by range(transaction_date)subpartition by hash(transaction_id)

subpartitions store in (dinya_space dinya_space dinya_space )

(

partition part_ values less than(to_date( yyyy mm dd ))

partition part_ values less than(to_date( yyyy mm dd ))

partition part_ values less than(maxvalue)

);

Table created

該例中 先是根據(jù)交易日期進(jìn)行范圍分區(qū) 然后根據(jù)交易的ID將記錄散列地存儲在三個表空間中

分區(qū)表操作

以上了解了三種分區(qū)表的建表方法 下面將使用實際的數(shù)據(jù)并針對按日期的范圍分區(qū)來測試分區(qū)表的數(shù)據(jù)記錄的操作

插入記錄

以下為引用的內(nèi)容

SQL insert into dinya_test values( BOOKS sysdate);

row created

SQL insert into dinya_test values( BOOKS sysdate+ );

row created

SQL insert into dinya_test values( BOOKS to_date( yyyy mm dd ));

row created

SQL insert into dinya_test values( BOOKS to_date( yyyy mm dd ));

row created

SQL insert into dinya_test values( BOOKS to_date( yyyy mm dd ));

row created

SQL insert into dinya_test values( BOOKS to_date( yyyy mm dd ));

row created

SQL mit;

Commit plete

SQL

按上面的建表結(jié)果 年前的數(shù)據(jù)將存儲在第一個分區(qū)part_ 上 而 年到 年的交易數(shù)據(jù)將存儲在第二個分區(qū)part_ 上 年以后的記錄存儲在第三個分區(qū)part_ 上

查詢分區(qū)表記錄 以下為引用的內(nèi)容

SQL select * from dinya_test partition(part_ );

TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE

BOOKS : :

BOOKS : :

SQL

SQL select * from dinya_test partition(part_ );

TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE

BOOKS

BOOKS

SQL

SQL select * from dinya_test partition(part_ );

TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE

BOOKS

BOOKS

SQL

從查詢的結(jié)果可以看出 插入的數(shù)據(jù)已經(jīng)根據(jù)交易時間范圍存儲在不同的分區(qū)中 這里是指定了分區(qū)的查詢 當(dāng)然也可以不指定分區(qū) 直接執(zhí)行select * from dinya_test查詢?nèi)坑涗?/p>

在也檢索的數(shù)據(jù)量很大的時候 指定分區(qū)會大大提高檢索速度

更新分區(qū)表的記錄

以下為引用的內(nèi)容

SQL update dinya_test partition(part_ ) t set em_description= DESK where

t transaction_id= ;

row updated

SQL mit;

Commit plete

SQL

這里將第一個分區(qū)中的交易ID= 的記錄中的item_description字段更新為 DESK 可以看到已經(jīng)成功更新了一條記錄 但是當(dāng)更新的時候指定了分區(qū) 而根據(jù)查詢的記錄不在該分區(qū)中時 將不會更新數(shù)據(jù) 請看下面的例子 以下為引用的內(nèi)容

SQL update dinya_test partition(part_ ) t set em_description= DESK where

t transaction_id= ;

rows updated

SQL mit;

Commit plete

SQL

指定了在第一個分區(qū)中更新記錄 但是條件中限制交易ID為 而查詢?nèi)?交易ID為 的記錄在第三個分區(qū)中 這樣該條語句將不會更新記錄

刪除分區(qū)表記錄

以下為引用的內(nèi)容

SQL delete from dinya_test partition(part_ ) t where t transaction_id= ;

row deleted

SQL mit;

Commit plete

SQL

上面例子刪除了第二個分區(qū)part_ 中的交易記錄ID為 的一條記錄 和更新數(shù)據(jù)相同 如果指定了分區(qū) 而條件中的數(shù)據(jù)又不在該分區(qū)中時 將不會刪除任何數(shù)據(jù)

分區(qū)表索引的使用

分區(qū)表和一般表一樣可以建立索引 分區(qū)表可以創(chuàng)建局部索引和全局索引 當(dāng)分區(qū)中出現(xiàn)許多事務(wù)并且要保證所有分區(qū)中的數(shù)據(jù)記錄的唯一性時采用全局索引

局部索引分區(qū)的建立

以下為引用的內(nèi)容

SQL create index dinya_idx_t on dinya_test(item_id)

local

(

partition idx_ tablespace dinya_space

partition idx_ tablespace dinya_space

partition idx_ tablespace dinya_space

);

Index created

SQL

看查詢的執(zhí)行計劃 從下面的執(zhí)行計劃可以看出 系統(tǒng)已經(jīng)使用了索引

以下為引用的內(nèi)容

SQL select * from dinya_test partition(part_ ) t where em_id= ;

Execution Plan

SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes= )

TABLE ACCESS (BY LOCAL INDEX ROWID) OF DINYA_TEST (Cost=

Card= Bytes= )

INDEX (RANGE SCAN) OF DINYA_IDX_T (NON UNIQUE) (Cost=

Card= )

Statistics

recursive calls

db block gets

consistent gets

physical reads

redo size

bytes sent via SQL*Net to client

bytes received via SQL*Net from client

SQL*Net roundtrips to/from client

sorts (memory)

sorts (disk)

rows processed

SQL

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

全局索引建立時global 子句允許指定索引的范圍值 這個范圍值為索引字段的范圍值

以下為引用的內(nèi)容

SQL create index dinya_idx_t on dinya_test(item_id)

global partition by range(item_id)

(

partition idx_ values less than ( ) tablespace dinya_space

partition idx_ values less than ( ) tablespace dinya_space

partition idx_ values less than (maxvalue) tablespace dinya_space

);

Index created

SQL

本例中對表的item_id字段建立索引分區(qū) 當(dāng)然也可以不指定索引分區(qū)名直接對整個表建立索引 如

以下為引用的內(nèi)容

SQL create index dinya_idx_t on dinya_test(item_id);

Index created

SQL

同樣的 對全局索引根據(jù)執(zhí)行計劃可以看出索引已經(jīng)可以使用

以下為引用的內(nèi)容

SQL select * from dinya_test t where em_id= ;

Execution Plan

SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes= )

TABLE ACCESS (BY GLOBAL INDEX ROWID) OF DINYA_TEST (Cost

= Card= Bytes= )

INDEX (RANGE SCAN) OF DINYA_IDX_T (NON UNIQUE) (Cost=

Card= )

Statistics

recursive calls

db block gets

consistent gets

physical reads

redo size

bytes sent via SQL*Net to client

bytes received via SQL*Net from client

SQL*Net roundtrips to/from client

sorts (memory)

sorts (disk)

rows processed

SQL

分區(qū)表的維護(hù)

了解了分區(qū)表的建立 索引的建立 表和索引的使用后 在應(yīng)用的還要經(jīng)常對分區(qū)進(jìn)行維護(hù)和管理 日常維護(hù)和管理的內(nèi)容包括 增加一個分區(qū) 合并一個分區(qū)及刪除分區(qū)等等 下面以范圍分區(qū)為例說明增加 合并 刪除分區(qū)的一般操作

增加一個分區(qū):

以下為引用的內(nèi)容

SQL alter table dinya_test

add partition part_ values less than(to_date( yyyy mm dd ))

tablespace dinya_spa

ce ;

Table altered

SQL

增加一個分區(qū)的時候 增加的分區(qū)的條件必須大于現(xiàn)有分區(qū)的最大值 否則系統(tǒng)將提示ORA partition bound must collate higher than that of the last partition 錯誤

合并一個分區(qū)

以下為引用的內(nèi)容

SQL alter table dinya_test merge partitions part_ part_ into partition part_ ;

Table altered

SQL

在本例中將原有的表的part_ 分區(qū)和part_ 分區(qū)進(jìn)行了合并 合并后的分區(qū)為part_ 如果在合并的時候把合并后的分區(qū)定為part_ 的時候 系統(tǒng)將提示ORA cannot reuse lower bound partition as resulting partition 錯誤

刪除分區(qū)

以下為引用的內(nèi)容

SQL alter table dinya_test drop partition part_ ;

Table altered

SQL

刪除分區(qū)表的一個分區(qū)后 查詢該表的數(shù)據(jù)時顯示 該分區(qū)中的數(shù)據(jù)已全部丟失 所以執(zhí)行刪除分區(qū)動作時要慎重 確保先備份數(shù)據(jù)后再執(zhí)行 或?qū)⒎謪^(qū)合并

總結(jié)

lishixinzhi/Article/program/Oracle/201311/17329

Oracle分區(qū)表怎么建唯

1、一般分區(qū)表都會很大，所以可以先創(chuàng)建表空間，為了讓分區(qū)表存放到單獨(dú)的表空間，否則默認(rèn)會存放到USERS表空間

2、創(chuàng)建TABLESPACE TS1：

CREATE TABLESPACE TS1 DATAFILE '/data1/oracle/test.dbf' SIZE 512M AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED；

3、創(chuàng)建分區(qū)表，需要確定按什么分區(qū)，比如按id或按時間段：

CREATE TABLE test_201602

(

ID NUMBER(10) NOT NULL,

CREATE_TIME DATE,

)

TABLESPACE TS1

PARTITION BY LIST (ID)

(PARTITION PT_1001 VALUES (1001) TABLESPACE TS1)；

4、繼續(xù)增加分區(qū)

ALTER TABLE test_201602 ADD PARTITION "PT_1003" VALUES (1003) LOGGING NOCOMPRESS;

ALTER TABLE test_201602 ADD PARTITION "PT_1004" VALUES (1004) LOGGING NOCOMPRESS;

5、查詢表及分區(qū)數(shù)量

select TABLE_NAME,PARTITION_COUNT,DEF_TABLESPACE_NAME from USER_PART_TABLES order by DEF_TABLESPACE_NAME;

本文題目：oracle怎么做分區(qū)表 oracle怎么創(chuàng)建表分區(qū)
文章路徑：http://chinadenli.net/article40/hgdpho.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站排名、靜態(tài)網(wǎng)站、服務(wù)器托管、小程序開發(fā)、網(wǎng)頁設(shè)計公司、定制開發(fā)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)