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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Oracle的普通表沒有辦法通過修改屬性的方式直接轉(zhuǎn)化為分區(qū)表，必須通過重建的方式進(jìn)行轉(zhuǎn)變，下面介紹三種效率比較高的方法，并說明它們各自的特點(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)：方法簡單易用，由于采用DDL語句，不會產(chǎn)生UNDO，且只產(chǎn)生少量REDO，效率相對較高，而且建表完成后數(shù)據(jù)已經(jīng)在分布到各個(gè)分區(qū)中了。

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

適用于修改不頻繁的表，在閑時(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)：只是對數(shù)據(jù)字典中分區(qū)和表的定義進(jìn)行了修改，沒有數(shù)據(jù)的修改或復(fù)制，效率最高。如果對數(shù)據(jù)在分區(qū)中的分布沒有進(jìn)一步要求的話，實(shí)現(xiàn)比較簡單。在執(zhí)行完RENAME操作后，可以檢查T_OLD中是否存在數(shù)據(jù)，如果存在的話，直接將這些數(shù)據(jù)插入到T中，可以保證對T插入的操作不會丟失。

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

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

方法三：Oracle9i以上版本，利用在線重定義功能

步驟：

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 過程已成功完成。

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 過程已成功完成。

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

PL/SQL 過程已成功完成。

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)的靈活性，對各種不同的需要都能滿足。而且，可以在切換前進(jìn)行相應(yīng)的授權(quán)并建立各種約束，可以做到切換完成后不再需要任何額外的管理操作。

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

適用于各種情況。

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

Oracle的在線重定義表功能：

Oracle的在線重定義表功能（二）：

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

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

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

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

第一是已經(jīng)存在的表沒有方法可以直接轉(zhuǎn)化為分區(qū)表。不過Oracle提供了在線重定義表的功能，可以通過這種方式來完成普通表到分區(qū)表的轉(zhuǎn)化。可以參考這個(gè)例子：

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

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

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

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

對于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)上查到的，測試了下確實(shí)可以用。

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)點(diǎn)

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

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

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

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

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

·范圍分區(qū)

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

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

下面將以實(shí)例的方式分別對這三種分區(qū)方法來說明分區(qū)表的使用 為了測試方便 我們先建三個(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ū)就是對數(shù)據(jù)表中的某個(gè)值的范圍進(jìn)行分區(qū) 根據(jù)某個(gè)值的范圍 決定將該數(shù)據(jù)存儲在哪個(gè)分區(qū)上 如根據(jù)序號分區(qū) 根據(jù)業(yè)務(wù)記錄的創(chuàng)建日期進(jìn)行分區(qū)等

需求描述 有一個(gè)物料交易表 表名 material_transactions 該表將來可能有千萬級的數(shù)據(jù)記錄數(shù) 要求在建該表的時(shí)候使用分區(qū)表 這時(shí)候我們可以使用序號分區(qū)三個(gè)區(qū) 每個(gè)區(qū)中預(yù)計(jì)存儲三千萬的數(shù)據(jù) 也可以使用日期分區(qū) 如每五年的數(shù)據(jù)存儲在一個(gè)分區(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在三千萬以下的記錄將存儲在第一個(gè)表空間dinya_space 中 分區(qū)名為:par_ 在三千萬到六千萬之間的記錄存儲在第二個(gè)表空間

dinya_space 中 分區(qū)名為 par_ 而交易ID在六千萬以上的記錄存儲在第三個(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

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

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

by range ( transaction_id transaction_date)

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

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

散列分區(qū)為通過指定分區(qū)編號來均勻分布數(shù)據(jù)的一種分區(qū)類型 因?yàn)橥ㄟ^在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ù)散列地存儲在三個(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將記錄散列地存儲在三個(gè)表空間中

分區(qū)表操作

以上了解了三種分區(qū)表的建表方法 下面將使用實(shí)際的數(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ù)將存儲在第一個(gè)分區(qū)part_ 上 而 年到 年的交易數(shù)據(jù)將存儲在第二個(gè)分區(qū)part_ 上 年以后的記錄存儲在第三個(gè)分區(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ù)交易時(shí)間范圍存儲在不同的分區(qū)中 這里是指定了分區(qū)的查詢 當(dāng)然也可以不指定分區(qū) 直接執(zhí)行select * from dinya_test查詢?nèi)坑涗?/p>

在也檢索的數(shù)據(jù)量很大的時(shí)候 指定分區(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

這里將第一個(gè)分區(qū)中的交易ID= 的記錄中的item_description字段更新為 DESK 可以看到已經(jīng)成功更新了一條記錄 但是當(dāng)更新的時(shí)候指定了分區(qū) 而根據(jù)查詢的記錄不在該分區(qū)中時(shí) 將不會更新數(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

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

刪除分區(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í) 將不會刪除任何數(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

看查詢的執(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

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

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

SQL create index dinya_idx_t on dinya_test(item_id);

Index created

SQL

同樣的 對全局索引根據(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)常對分區(qū)進(jìn)行維護(hù)和管理 日常維護(hù)和管理的內(nèi)容包括 增加一個(gè)分區(qū) 合并一個(gè)分區(qū)及刪除分區(qū)等等 下面以范圍分區(qū)為例說明增加 合并 刪除分區(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ū)后 查詢該表的數(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ù)(互相遷移) 由于其號稱是采用了更改數(shù)據(jù)字典的方式 因此效率最高(幾乎不涉及io操作) Exchange partition適用于所有分區(qū)格式 你可以將數(shù)據(jù)從分區(qū)表遷移到非分區(qū)表 也可以從非分區(qū)表遷移至分區(qū)表 或者從hash partition到range partition諸如此類 其語法 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ū)會被置為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; 插入測試數(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ù)的常見SQL語句及注意事項(xiàng) 對于分區(qū)索引 不能整體進(jìn)行重建 只能對單個(gè)分區(qū)進(jìn)行重建(也就是物理存在的分區(qū)) 語法如下 Alter index idx_name rebuild partition index_partition_name [online nologging] Alter Index IndexName Rebuild Partition P_Name; 有子分區(qū)的本地索引 不能重建某分區(qū) 只能對每個(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ū)會自動(dòng)添加 新加分區(qū)后 該區(qū)沒有統(tǒng)計(jì)信息 全是空 如果表級不是global_satus 則表級的統(tǒng)計(jì)信息也會空 新加分區(qū)后 如果表級統(tǒng)計(jì)是global_satus 還會出現(xiàn)out of range的問題（CBO估算的選擇率很低） 解決 問題的方法是 copy_table_stats exec dbms_stats copy_table_stats(user tabname = TEST_TAB srcpartname = P_ dstpartname = P_ );

tuncate and drop partition truncate和drop可對有子分區(qū)的分區(qū)進(jìn)行 ALTER TABLE TEST truncate Partition P_ ; ALTER TABLE TEST Drop Partition P_ ; 它們會導(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變了 會導(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沒有更新 split partion 語法 alter table table_name split partition partition_name at (value) into (partition partition_name partition partition_name) [update [global] indexes]; 可以對有子分區(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ū)表對于分區(qū)字段是不允許進(jìn)行update操作的，如果有對分區(qū)字段行進(jìn)update，就會報(bào)錯(cuò)——ORA-14402: 更新分區(qū)關(guān)鍵字列將導(dǎo)致分區(qū)的更改。但是可以通過打開表的row movement屬性來允許對分區(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ū)的更改

打開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://chinadenli.net/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)網(wǎng)站

廣告

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