oracle分區(qū)表如何每月自動(dòng)增加分區(qū)

在oracle中如果涉及到自動(dòng)的任務(wù)一般采用job的方法。

創(chuàng)新互聯(lián)長(zhǎng)期為近1000家客戶(hù)提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對(duì)不同對(duì)象提供差異化的產(chǎn)品和服務(wù)；打造開(kāi)放共贏平臺(tái)，與合作伙伴共同營(yíng)造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為延慶企業(yè)提供專(zhuān)業(yè)的成都網(wǎng)站制作、網(wǎng)站建設(shè)，延慶網(wǎng)站改版等技術(shù)服務(wù)。擁有10年豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開(kāi)發(fā)。

1 自己寫(xiě)一個(gè)存儲(chǔ)過(guò)程。這個(gè)存儲(chǔ)過(guò)程的目的就是給分區(qū)表增加一個(gè)分區(qū)。

2 新建一個(gè)Job，設(shè)定每個(gè)月某個(gè)時(shí)刻調(diào)用你寫(xiě)的存儲(chǔ)過(guò)程

這樣就能達(dá)到你每月自動(dòng)增加分區(qū)的需求。

oracle一張上千萬(wàn)記錄的數(shù)據(jù)表需要改為分區(qū)表，用什么方法好？

Oracle提供了分區(qū)技術(shù)以支持VLDB(Very Large DataBase)。分區(qū)表通過(guò)對(duì)分區(qū)列的判斷，把分區(qū)列不同的記錄，放到不同的分區(qū)中。分區(qū)完全對(duì)應(yīng)用透明。

Oracle的分區(qū)表可以包括多個(gè)分區(qū)，每個(gè)分區(qū)都是一個(gè)獨(dú)立的段（SEGMENT），可以存放到不同的表空間中。查詢(xún)時(shí)可以通過(guò)查詢(xún)表來(lái)訪問(wèn)各個(gè)分區(qū)中的數(shù)據(jù)，也可以通過(guò)在查詢(xún)時(shí)直接指定分區(qū)的方法來(lái)進(jìn)行查詢(xún)。

分區(qū)提供以下優(yōu)點(diǎn)：

由于將數(shù)據(jù)分散到各個(gè)分區(qū)中，減少了數(shù)據(jù)損壞的可能性；

可以對(duì)單獨(dú)的分區(qū)進(jìn)行備份和恢復(fù)；

可以將分區(qū)映射到不同的物理磁盤(pán)上，來(lái)分散IO；

提高可管理性、可用性和性能。

Oracle提供了以下幾種分區(qū)類(lèi)型：

范圍分區(qū)（range）；

哈希分區(qū)（hash）；

列表分區(qū)（list）；

范圍－哈希復(fù)合分區(qū)（range-hash）；

范圍－列表復(fù)合分區(qū)（range-list）。

Oracle的普通表沒(méi)有辦法通過(guò)修改屬性的方式直接轉(zhuǎn)化為分區(qū)表，必須通過(guò)重建的方式進(jìn)行轉(zhuǎn)變，下面介紹三種效率比較高的方法，并說(shuō)明它們各自的特點(diǎn)。

方法一：利用原表重建分區(qū)表。

步驟：

SQL CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

表已創(chuàng)建。

SQL INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

已創(chuàng)建6264行。

SQL COMMIT;

提交完成。

SQL CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)

2 (PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')),

3 PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')),

4 PARTITION P3 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),

5 PARTITION P4 VALUES LESS THAN (MAXVALUE))

6 AS SELECT ID, TIME FROM T;

表已創(chuàng)建。

SQL RENAME T TO T_OLD;

表已重命名。

SQL RENAME T_NEW TO T;

表已重命名。

SQL SELECT COUNT(*) FROM T;

COUNT(*)

----------

6264

SQL SELECT COUNT(*) FROM T PARTITION (P1);

COUNT(*)

----------

SQL SELECT COUNT(*) FROM T PARTITION (P2);

COUNT(*)

----------

6246

SQL SELECT COUNT(*) FROM T PARTITION (P3);

COUNT(*)

----------

18

優(yōu)點(diǎn)：方法簡(jiǎn)單易用，由于采用DDL語(yǔ)句，不會(huì)產(chǎn)生UNDO，且只產(chǎn)生少量REDO，效率相對(duì)較高，而且建表完成后數(shù)據(jù)已經(jīng)在分布到各個(gè)分區(qū)中了。

不足：對(duì)于數(shù)據(jù)的一致性方面還需要額外的考慮。由于幾乎沒(méi)有辦法通過(guò)手工鎖定T表的方式保證一致性，在執(zhí)行CREATE TABLE語(yǔ)句和RENAME T_NEW TO T語(yǔ)句直接的修改可能會(huì)丟失，如果要保證一致性，需要在執(zhí)行完語(yǔ)句后對(duì)數(shù)據(jù)進(jìn)行檢查，而這個(gè)代價(jià)是比較大的。另外在執(zhí)行兩個(gè)RENAME語(yǔ)句之間執(zhí)行的對(duì)T的訪問(wèn)會(huì)失敗。

適用于修改不頻繁的表，在閑時(shí)進(jìn)行操作，表的數(shù)據(jù)量不宜太大。

方法二：使用交換分區(qū)的方法。

步驟：

SQL CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

表已創(chuàng)建。

SQL INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

已創(chuàng)建6264行。

SQL COMMIT;

提交完成。

SQL CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)

2 (PARTITION P1 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),

3 PARTITION P2 VALUES LESS THAN (MAXVALUE));

表已創(chuàng)建。

SQL ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T;

表已更改。

SQL RENAME T TO T_OLD;

表已重命名。

SQL RENAME T_NEW TO T;

表已重命名。

SQL SELECT COUNT(*) FROM T;

COUNT(*)

----------

6264

優(yōu)點(diǎn)：只是對(duì)數(shù)據(jù)字典中分區(qū)和表的定義進(jìn)行了修改，沒(méi)有數(shù)據(jù)的修改或復(fù)制，效率最高。如果對(duì)數(shù)據(jù)在分區(qū)中的分布沒(méi)有進(jìn)一步要求的話(huà)，實(shí)現(xiàn)比較簡(jiǎn)單。在執(zhí)行完RENAME操作后，可以檢查T(mén)_OLD中是否存在數(shù)據(jù)，如果存在的話(huà)，直接將這些數(shù)據(jù)插入到T中，可以保證對(duì)T插入的操作不會(huì)丟失。

不足：仍然存在一致性問(wèn)題，交換分區(qū)之后RENAME T_NEW TO T之前，查詢(xún)、更新和刪除會(huì)出現(xiàn)錯(cuò)誤或訪問(wèn)不到數(shù)據(jù)。如果要求數(shù)據(jù)分布到多個(gè)分區(qū)中，則需要進(jìn)行分區(qū)的SPLIT操作，會(huì)增加操作的復(fù)雜度，效率也會(huì)降低。

適用于包含大數(shù)據(jù)量的表轉(zhuǎn)到分區(qū)表中的一個(gè)分區(qū)的操作。應(yīng)盡量在閑時(shí)進(jìn)行操作。

方法三：Oracle9i以上版本，利用在線(xiàn)重定義功能

步驟：

SQL CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

表已創(chuàng)建。

SQL INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

已創(chuàng)建6264行。

SQL COMMIT;

提交完成。

SQL EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T', DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL 過(guò)程已成功完成。

SQL CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)

2 (PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')),

3 PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')),

4 PARTITION P3 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),

5 PARTITION P4 VALUES LESS THAN (MAXVALUE));

表已創(chuàng)建。

SQL EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T', 'T_NEW', -

'ID ID, TIME TIME', DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL 過(guò)程已成功完成。

SQL EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('YANGTK', 'T', 'T_NEW');

PL/SQL 過(guò)程已成功完成。

SQL SELECT COUNT(*) FROM T;

COUNT(*)

----------

6264

SQL SELECT COUNT(*) FROM T PARTITION (P2);

COUNT(*)

----------

6246

SQL SELECT COUNT(*) FROM T PARTITION (P3);

COUNT(*)

----------

18

優(yōu)點(diǎn)：保證數(shù)據(jù)的一致性，在大部分時(shí)間內(nèi)，表T都可以正常進(jìn)行DML操作。只在切換的瞬間鎖表，具有很高的可用性。這種方法具有很強(qiáng)的靈活性，對(duì)各種不同的需要都能滿(mǎn)足。而且，可以在切換前進(jìn)行相應(yīng)的授權(quán)并建立各種約束，可以做到切換完成后不再需要任何額外的管理操作。

不足：實(shí)現(xiàn)上比上面兩種略顯復(fù)雜。

適用于各種情況。

這里只給出了在線(xiàn)重定義表的一個(gè)最簡(jiǎn)單的例子，詳細(xì)的描述和例子可以參考下面兩篇文章。

Oracle的在線(xiàn)重定義表功能：

Oracle的在線(xiàn)重定義表功能（二）：

索引也可以進(jìn)行分區(qū)，分區(qū)索引有兩種類(lèi)型：global和local。對(duì)于local索引，每一個(gè)表分區(qū)對(duì)應(yīng)一個(gè)索引分區(qū)，當(dāng)表的分區(qū)發(fā)生變化時(shí)，索引的維護(hù)由Oracle自動(dòng)進(jìn)行。對(duì)于global索引，可以選擇是否分區(qū)，而且索引的分區(qū)可以不與表分區(qū)相對(duì)應(yīng)。當(dāng)對(duì)分區(qū)進(jìn)行維護(hù)操作時(shí)，通常會(huì)導(dǎo)致全局索引的INVALDED，必須在執(zhí)行完操作后REBUILD。Oracle9i提供了UPDATE GLOBAL INDEXES語(yǔ)句，可以使在進(jìn)行分區(qū)維護(hù)的同時(shí)重建全局索引。

全局索引可以包含多個(gè)分區(qū)的值 局部索引比全局索引容易管理,而全局索引比較快

注意:不能為散列分區(qū) 或者 子分區(qū)創(chuàng)建全局索引

Oracle的分區(qū)功能十分強(qiáng)大。不過(guò)用起來(lái)發(fā)現(xiàn)有兩點(diǎn)不大方便：

第一是已經(jīng)存在的表沒(méi)有方法可以直接轉(zhuǎn)化為分區(qū)表。不過(guò)Oracle提供了在線(xiàn)重定義表的功能，可以通過(guò)這種方式來(lái)完成普通表到分區(qū)表的轉(zhuǎn)化?？梢詤⒖歼@個(gè)例子：

第二點(diǎn)是如果采用了local分區(qū)索引，那么在增加表分區(qū)的時(shí)候，索引分區(qū)的表空間是不可控制的。如果希望將表和索引的分區(qū)分開(kāi)到不同的表空間且不同索引分區(qū)也分散到不同的表空間中，那么只能在增加分區(qū)后，對(duì)新增的分區(qū)索引單獨(dú)rebuild。

Oracle最大允許存在多少個(gè)分區(qū)呢?

我們可以從Oracle的Concepts手冊(cè)上找到這個(gè)信息，對(duì)于Oracle9iR2:

Tables can be partitioned into up to 64,000 separate partitions.

對(duì)于Oracle10gR2，Oracle增強(qiáng)了分區(qū)特性：

Tables can be partitioned into up to 1024K-1 separate partitions.

關(guān)于何時(shí)應(yīng)該進(jìn)行分區(qū),Oracle有如下建議:

■ Tables greater than 2GB should always be considered for partitioning.

■ 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.

這些信息是在網(wǎng)上查到的，測(cè)試了下確實(shí)可以用。

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

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

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

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

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

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

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

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

·范圍分區(qū)

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

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

下面將以實(shí)例的方式分別對(duì)這三種分區(qū)方法來(lái)說(shuō)明分區(qū)表的使用 為了測(cè)試方便 我們先建三個(gè)表空間

以下為引用的內(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ū)就是對(duì)數(shù)據(jù)表中的某個(gè)值的范圍進(jìn)行分區(qū) 根據(jù)某個(gè)值的范圍 決定將該數(shù)據(jù)存儲(chǔ)在哪個(gè)分區(qū)上 如根據(jù)序號(hào)分區(qū) 根據(jù)業(yè)務(wù)記錄的創(chuàng)建日期進(jìn)行分區(qū)等

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

根據(jù)交易記錄的序號(hào)分區(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ù)交易的序號(hào) 交易ID在三千萬(wàn)以下的記錄將存儲(chǔ)在第一個(gè)表空間dinya_space 中 分區(qū)名為:par_ 在三千萬(wàn)到六千萬(wàn)之間的記錄存儲(chǔ)在第二個(gè)表空間

dinya_space 中 分區(qū)名為 par_ 而交易ID在六千萬(wàn)以上的記錄存儲(chǔ)在第三個(gè)表空間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

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

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

by range ( transaction_id transaction_date)

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

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

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

以下為引用的內(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í)插入數(shù)據(jù) 系統(tǒng)將按transaction_id將記錄散列地插入三個(gè)分區(qū)中 這里也就是三個(gè)不同的表空間中

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

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

以下為引用的內(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將記錄散列地存儲(chǔ)在三個(gè)表空間中

分區(qū)表操作

以上了解了三種分區(qū)表的建表方法 下面將使用實(shí)際的數(shù)據(jù)并針對(duì)按日期的范圍分區(qū)來(lái)測(cè)試分區(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ù)將存儲(chǔ)在第一個(gè)分區(qū)part_ 上 而 年到 年的交易數(shù)據(jù)將存儲(chǔ)在第二個(gè)分區(qū)part_ 上 年以后的記錄存儲(chǔ)在第三個(gè)分區(qū)part_ 上

查詢(xún)分區(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

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

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

更新分區(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

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

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

t transaction_id= ;

rows updated

SQL mit;

Commit plete

SQL

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

刪除分區(qū)表記錄

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

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

row deleted

SQL mit;

Commit plete

SQL

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

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

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

局部索引分區(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

看查詢(xún)的執(zhí)行計(jì)劃 從下面的執(zhí)行計(jì)劃可以看出 系統(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ū)的建立

全局索引建立時(shí)global 子句允許指定索引的范圍值 這個(gè)范圍值為索引字段的范圍值

以下為引用的內(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

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

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

SQL create index dinya_idx_t on dinya_test(item_id);

Index created

SQL

同樣的 對(duì)全局索引根據(jù)執(zhí)行計(jì)劃可以看出索引已經(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)常對(duì)分區(qū)進(jìn)行維護(hù)和管理 日常維護(hù)和管理的內(nèi)容包括 增加一個(gè)分區(qū) 合并一個(gè)分區(qū)及刪除分區(qū)等等 下面以范圍分區(qū)為例說(shuō)明增加 合并 刪除分區(qū)的一般操作

增加一個(gè)分區(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

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

合并一個(gè)分區(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_ 如果在合并的時(shí)候把合并后的分區(qū)定為part_ 的時(shí)候 系統(tǒng)將提示ORA cannot reuse lower bound partition as resulting partition 錯(cuò)誤

刪除分區(qū)

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

SQL alter table dinya_test drop partition part_ ;

Table altered

SQL

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

總結(jié)

lishixinzhi/Article/program/Oracle/201311/17329

Oracle分區(qū)之四：分區(qū)維護(hù)和管理

一 分區(qū)表的相關(guān)實(shí)驗(yàn) 創(chuàng)建一個(gè)列表分區(qū)表 create table t (id number city varchar ( )) partition by list(city) ( partition p values ( SH JS ZJ ) partition p values ( BJ TJ HB ) partition p values ( GZ SZ ) partition p_others values (default) ); create or replace procedure proc as begin for i in loop execute immediate INSERT INTO T values(:x :y) USING i SH ; end loop; end; / exec proc create or replace procedure proc as begin for i in loop execute immediate INSERT INTO T values(:x :y) USING i JS ; end loop; end; / exec proc create or replace procedure proc as begin for i in loop execute immediate INSERT INTO T values(:x :y) USING i ZJ ; end loop; end; / exec proc create or replace procedure proc as begin for i in loop execute immediate INSERT INTO T values(:x :y) USING i BJ ; end loop; end; / exec proc create or replace procedure proc as begin for i in loop execute immediate INSERT INTO T values(:x :y) USING i TJ ; end loop; end; / exec proc create or replace procedure proc as begin for i in loop execute immediate INSERT INTO T values(:x :y) USING i GZ ; end loop; end; / exec proc create or replace procedure proc as begin for i in loop execute immediate INSERT INTO T values(:x :y) USING i HB ; end loop; end; / exec proc create or replace procedure proc as begin for i in loop execute immediate INSERT INTO T values(:x :y) USING i SZ ; end loop; end; / exec proc create or replace procedure proc as begin for i in loop execute immediate INSERT INTO T values(:x :y) USING i AH ; end loop; end; / exec proc SQL SET linesize SQL select TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT from dba_tab_partitions where table_name= T ; TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT T HR P T HR P T HR P T HR P_OTHERS

實(shí)驗(yàn)一(SPLIT 分區(qū)) alter table t split partition p values ( JS ) into (partition p _ partition p _ ); SQL select TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT from dba_tab_partitions where table_name= T ; TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT T HR P _ T HR P _ T HR P T HR P T HR P_OTHERS

實(shí)驗(yàn)二(merge 分區(qū)) alter table t merge partitions p _ p _ into partition p ; SQL select TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT from dba_tab_partitions where table_name= T ; TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT T HR P T HR P T HR P T HR P_OTHERS 實(shí)驗(yàn)三 alter table t split partition p values ( BJ TJ ) into (partition p _ partition p _ ); SQL select TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT from dba_tab_partitions where table_name= T ; TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT T HR P T HR P _ T HR P _ T HR P T HR P_OTHERS

實(shí)驗(yàn)四 alter table t merge partitions p _ p _ into partition p ; SQL select TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT from dba_tab_partitions where table_name= T ; TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT T HR P T HR P T HR P T HR P_OTHERS 實(shí)驗(yàn)五( 向分區(qū)某個(gè)分區(qū)里增加個(gè)分區(qū)列值) SQL alter table t modify partition p add values( ZQ ); Table altered 二 分區(qū)索引的相關(guān)實(shí)驗(yàn) 實(shí)驗(yàn)六(創(chuàng)建索引分區(qū)) create index idx_t on t (id) global partition by range(id) ( partition p values less than ( ) partition p values less than (maxvalue) ); drop index idx_ create index idx_t on t (id) global partition by hash(id) partitions ; create table tt (id number createdate date) partition by range(createdate) subpartition by hash(id) subpartitions ( partition p values less than (to_date( yyyy mm dd )) partition p values less than (to_date( yyyy mm dd )) ); create table tt (id number name varchar ( )) partition by range(name) ( partition p values less than ( h ) partition p values less than ( o ) ); create index idx_tt on tt (id) local; drop indexe idx_tt ; create index idx_tt on tt (id) global partition by range(id) ( partition p values less than ( ) partition p values less than (maxvalue) ); SQL SET LINESIZE SQL select INDEX_OWNER INDEX_NAME PARTITION_NAME FROM dba_Ind_Partitions where index_name= IDX_TT ; INDEX_OWNER INDEX_NAME PARTITION_NAME HR IDX_TT P HR IDX_TT P

alter index idx_tt split partition p at ( ) into (partition p partition p_max); SQL select INDEX_OWNER INDEX_NAME PARTITION_NAME FROM dba_Ind_Partitions where index_name= IDX_TT ; INDEX_OWNER INDEX_NAME PARTITION_NAME HR IDX_TT P HR IDX_TT P HR IDX_TT P_MAX

三 分區(qū)表交換的相關(guān)實(shí)驗(yàn) Exchange partition提供了一種方式 讓你在表與表或分區(qū)與分區(qū)之間遷移數(shù)據(jù) 注意不是將表轉(zhuǎn)換成分區(qū)或非分區(qū)的形式 而僅只是遷移表中數(shù)據(jù)(互相遷移) 由于其號(hào)稱(chēng)是采用了更改數(shù)據(jù)字典的方式 因此效率最高(幾乎不涉及io操作) Exchange partition適用于所有分區(qū)格式 你可以將數(shù)據(jù)從分區(qū)表遷移到非分區(qū)表 也可以從非分區(qū)表遷移至分區(qū)表 或者從hash partition到range partition諸如此類(lèi) 其語(yǔ)法 alter table tbname exchange partition/subpartition ptname with table tbname ; 注意 在將未分區(qū)表的數(shù)據(jù)遷移到分區(qū)表中時(shí) 可能出現(xiàn)ora 的錯(cuò)誤 雖然可以用without validation去解決 但是此時(shí)進(jìn)入分區(qū)表的數(shù)據(jù)可能不符合分區(qū)規(guī) 則 所以without validation一定要慎用 a 涉及交換的兩表之間表結(jié)構(gòu)必須一致 除非附加with validation子句; b 如果是從非分區(qū)表向分區(qū)表做交換 非分區(qū)表中的數(shù)據(jù)必須符合分區(qū)表中指定分區(qū)的規(guī)則 除非附加without validation子句; c 如果從分區(qū)表向分區(qū)表做交換 被交換的分區(qū)的數(shù)據(jù)必須符合分區(qū)規(guī)則 除非附加without validation子句; d Global索引或涉及到數(shù)據(jù)改動(dòng)了的global索引分區(qū)會(huì)被置為unusable 除非附加update indexes子句 注意 一旦附加了without validation子句 則表示不再驗(yàn)證數(shù)據(jù)有效性 因此指定該子句時(shí)務(wù)必慎重

創(chuàng)建一個(gè)交換分區(qū)的普通heap表 SQL create table exchange_t (id number city varchar ( )); Table created SQL select distinct city from t partition (p ); CITY TJ BJ HB 查看下P 分區(qū)有records SQL select count(*) from t partition (p ); COUNT(*) 下面是分區(qū)表和普通HEAP表交換 alter table t exchange partition p with table exchange_t including indexes without validation; 驗(yàn)證下數(shù)據(jù) 和上面的P 分區(qū)數(shù)據(jù)一致 SQL select count(*) from exchange_t ; COUNT(*) SQL select distinct city from exchange_t ; CITY TJ BJ HB 四 一個(gè)實(shí)際應(yīng)用的例子的相關(guān)實(shí)驗(yàn) 創(chuàng)建一個(gè)分區(qū)表 只保留最近 年的財(cái)務(wù)數(shù)據(jù) create table ware(wareyear varchar ( ) id number) partition by range (wareyear) ( partition p_ values less than( ) partition p_ values less than( ) partition p_max values less than(maxvalue) ); 創(chuàng)建索引 create index idx_ware_id on ware(id) global partition by range(id) ( partition p_id_ values less than( ) partition p_id_max values less than(maxvalue) ); create index idx_ware_wareyear on ware(wareyear) local; 插入測(cè)試數(shù)據(jù) insert into ware select object_id from dba_objects; insert into ware select object_id from dba_objects; mit; 年終 歸檔最早的數(shù)據(jù) 并加入新財(cái)年的數(shù)據(jù) create table ware_ (wareyear varchar ( ) id number); create index idx_ware_ on ware_ (wareyear); insert into ware_ select object_id from dba_objects; mit; alter table ware split partition p_max at ( ) into (partition p_ partition p_max); 將p_ 分區(qū)放入ware_ 表里 alter table ware exchange partition p_ with table ware_ including indexes without validation; create table ware_ (wareyear varchar ( ) id number); create index idx_ware_ on ware_ (wareyear); alter table ware exchange partition p_ with table ware_ including indexes without validation; 刪除p_ 分區(qū) alter table ware drop partition p_ ; 導(dǎo)出做歸檔 [oracle@even admin]$ exp hr/hr@test file=/home/oracle/ware_ dmp tables=ware_ press=n Export: Release Production on Fri Jan : : Copyright (c) Oracle All rights reserved

Connected to: Oracle Database g Enterprise Edition Release Production With the Partitioning Oracle Label Security OLAP and Data Mining options Export done in US ASCII character set and AL UTF NCHAR character set server uses AL UTF character set (possible charset conversion) About to export specified tables via Conventional Path exporting table WARE_ rows exported Export terminated successfully without warnings 然后刪除表 drop table ware_ ;

五 表和索引的維護(hù)的常見(jiàn)SQL語(yǔ)句及注意事項(xiàng) 對(duì)于分區(qū)索引 不能整體進(jìn)行重建 只能對(duì)單個(gè)分區(qū)進(jìn)行重建(也就是物理存在的分區(qū)) 語(yǔ)法如下 Alter index idx_name rebuild partition index_partition_name [online nologging] Alter Index IndexName Rebuild Partition P_Name; 有子分區(qū)的本地索引 不能重建某分區(qū) 只能對(duì)每個(gè)子分區(qū)進(jìn)行重建 Alter Index Index_Name Rebuild subPartition P_Sub_Name; 腳本 重建所有unUsable的索引 Select alter index || Index_Name || rebuild; From User_Indexes Where Status = UNUSABLE union Select alter index || Index_Name || rebuild Partition ||Partition_Name || ; From User_Ind_Partitions Where Status = UNUSABLE union Select alter index || Index_Name || rebuild subPartition ||subPartition_Name || ; From User_Ind_subPartitions Where Status = UNUSABLE ;

add parttion Alter Table TestTab Add Partition P Values Less Than ( ); 如果有子分區(qū) 且定義了子分區(qū)模板 所有的子分區(qū)會(huì)自動(dòng)添加 新加分區(qū)后 該區(qū)沒(méi)有統(tǒng)計(jì)信息 全是空 如果表級(jí)不是global_satus 則表級(jí)的統(tǒng)計(jì)信息也會(huì)空 新加分區(qū)后 如果表級(jí)統(tǒng)計(jì)是global_satus 還會(huì)出現(xiàn)out of range的問(wèn)題（CBO估算的選擇率很低） 解決 問(wèn)題的方法是 copy_table_stats exec dbms_stats copy_table_stats(user tabname = TEST_TAB srcpartname = P_ dstpartname = P_ );

tuncate and drop partition truncate和drop可對(duì)有子分區(qū)的分區(qū)進(jìn)行 ALTER TABLE TEST truncate Partition P_ ; ALTER TABLE TEST Drop Partition P_ ; 它們會(huì)導(dǎo)致globl index的某些分區(qū)不可用 必須這樣做 ALTER TABLE TEST truncate Partition P_ update indexes; ALTER TABLE TEST truncate Partition P_ update global indexes; ALTER TABLE TEST Drop Partition P_ update indexes; ALTER TABLE TEST Drop Partition P_ update global indexes;

move partition 有子分區(qū)的分區(qū)不能move 只能move每個(gè)子分區(qū)(也就是物理分區(qū)) Alter Table TEST Move Partition P_ ; 由于rowid變了 會(huì)導(dǎo)致所有相關(guān)索引unusable 必須這樣做 Alter Table TEST Move subPartition P_ _P update indexes; Alter Table TEST Move subPartition P_ _P update global indexes; Local Index沒(méi)有更新 split partion 語(yǔ)法 alter table table_name split partition partition_name at (value) into (partition partition_name partition partition_name) [update [global] indexes]; 可以對(duì)有子分區(qū)的分區(qū)進(jìn)行 自動(dòng)split子分區(qū) 由于rowid變了 新分區(qū)和global index都變?yōu)閡nusable alter table t merge partitions p _ p _ into partition p ; 合并range分區(qū) ALTER TABLE Test_Tab Merge Partitions P_ P_ Into Partition P_ [Update [global] Indexes]; 該分區(qū)有子分區(qū) 有子分區(qū) 也可以單獨(dú)合并子分區(qū)merge subpartition

lishixinzhi/Article/program/Oracle/201311/19037

oracle怎么更新分區(qū)表的數(shù)據(jù)

默認(rèn)情況下，oracle的分區(qū)表對(duì)于分區(qū)字段是不允許進(jìn)行update操作的，如果有對(duì)分區(qū)字段行進(jìn)update，就會(huì)報(bào)錯(cuò)——ORA-14402: 更新分區(qū)關(guān)鍵字列將導(dǎo)致分區(qū)的更改。但是可以通過(guò)打開(kāi)表的row movement屬性來(lái)允許對(duì)分區(qū)字段的update操作。

例：創(chuàng)建分區(qū)表test_part進(jìn)行實(shí)驗(yàn)

create table TEST_PART

(

A1 NUMBERnot null,

A2 DATE not null,

A3 VARCHAR2(6) not null,

A4 DATE not null,

A5 NUMBER not null,

)

partition by range (A1)

(

partition P1 values less than (1000),

partition P2 values less than (2000),

partition P3 values less than (3000),

partition P4 values less than (4000),

partition P5 values less than (5000),

partition P6 values less than (MAXVALUE)

);

插入如下的數(shù)據(jù)

SQL select * from test_part;

A1 A2 A3 A4 A5

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

123 2006-06-30 123456 2006-06-30 123

456 2006-06-30 asdfgh 2006-06-30 456

1 2006-06-30 234123 2006-06-30 1

2 2006-06-30 234234 2006-06-30 2

1234 2006-06-30 456789 2006-06-30 1234

1111 2006-06-30 ewrqwe 2006-06-30 1111

2222 2006-06-30 fdafda 2006-06-30 2222

3333 2006-06-30 342342 2006-06-30 3333

5678 2006-06-30 qwerty 2006-06-30 5678

9 rows selected

分區(qū)P1、P2的數(shù)據(jù)分別為：

SQL select rowid,t.* from test_part partition(p1) t;

ROWID A1 A2 A3 A4 A5

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

AAAGLoAAGAAAtsEAAB 456 2006-06-30 asdfgh 2006-06-30 456

AAAGLoAAGAAAtsEAAC 1 2006-06-30 234123 2006-06-30 1

AAAGLoAAGAAAtsEAAD 2 2006-06-30 234234 2006-06-30 2

AAAGLoAAGAAAtsEAAE 123 2006-06-30 123456 2006-06-30 123

SQL select rowid,t.* from test_part partition(p2) t;

ROWID A1 A2 A3 A4 A5

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

AAAGLwAAGAAA+8MAAC 1234 2006-06-30 456789 2006-06-30 1234

AAAGLwAAGAAA+8MAAD 1111 2006-06-30 ewrqwe 2006-06-30 1111

直接update提示錯(cuò)誤

SQL update test_part set a1=1123 where a1=123;

update test_part set a1=1123 where a1=123

ORA-14402: 更新分區(qū)關(guān)鍵字列將導(dǎo)致分區(qū)的更改

打開(kāi)row movement屬性

SQL alter table test_part enable row movement;

Table altered

再次執(zhí)行update操作

SQL update test_part set a1=1123 where a1=123;

1 row updated

執(zhí)行是成功的并遷移到分區(qū)P2上了，且這時(shí)候rowid也發(fā)生了變化

SQL select rowid,t.* from test_part partition(p2) t;

ROWID A1 A2 A3 A4 A5

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

AAAGLwAAGAAA+8MAAC 1234 2006-06-30 456789 2006-06-30 1234

AAAGLwAAGAAA+8MAAD 1111 2006-06-30 ewrqwe 2006-06-30 1111

AAAGLwAAGAAA+8PAAB 1123 2006-06-30 123456 2006-06-30 123

SQL

enable row movement可以允許數(shù)據(jù)段的壓縮、update分區(qū)字段的數(shù)據(jù)（跨分區(qū)的）

當(dāng)前文章：oracle如何更新分區(qū) oracle如何刪除原有分區(qū),新建分區(qū)
當(dāng)前網(wǎng)址：http://aaarwkj.com/article44/hhhghe.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站排名、網(wǎng)站收錄、標(biāo)簽優(yōu)化、外貿(mào)網(wǎng)站建設(shè)、網(wǎng)站營(yíng)銷(xiāo)、自適應(yīng)網(wǎng)站

廣告

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