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

專注于為中小企業(yè)提供成都做網(wǎng)站、成都網(wǎng)站制作服務(wù),電腦端+手機(jī)端+微信端的三站合一,更高效的管理,為中小企業(yè)輝南免費(fèi)做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動(dòng)了上千余家企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網(wǎng)站建設(shè)實(shí)現(xiàn)規(guī)模擴(kuò)充和轉(zhuǎn)變。

實(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)稱是采用了更改數(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ū)會(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; 插入測試數(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) 對(duì)于分區(qū)索引 不能整體進(jìn)行重建 只能對(duì)單個(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ū) 只能對(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ū)沒有統(tǒng)計(jì)信息 全是空 如果表級(jí)不是global_satus 則表級(jí)的統(tǒng)計(jì)信息也會(huì)空 新加分區(qū)后 如果表級(jí)統(tǒng)計(jì)是global_satus 還會(huì)出現(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可對(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沒有更新 split partion 語法 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ū)

一.表分區(qū)策略

1.識(shí)別大表

采用ANALYZE TABLE語句進(jìn)行分析，然后查詢數(shù)據(jù)字典獲得相應(yīng)的數(shù)據(jù)量。

2.大表如何分區(qū)

可根據(jù)月份，季度以及年份等進(jìn)行分區(qū)；

3.分區(qū)的表空間規(guī)劃

要對(duì)每個(gè)表空間的大小進(jìn)行估計(jì)

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

a.創(chuàng)建范圍分區(qū)的關(guān)鍵字是'RANGE'

1.范圍分區(qū)

create table ware_retail_part --創(chuàng)建一個(gè)描述商品零售的數(shù)據(jù)表

(

id integer primary key,--銷售編號(hào)

retail_date date,--銷售日期

ware_name varchar2(50)--商品名稱

)

partition by range(retail_date)

(

--2011年第一個(gè)季度為part_01分區(qū)

partition par_01 values less than(to_date('2011-04-01','yyyy-mm-dd')) tablespace TEMP01,

--2011年第二個(gè)季度為part_02分區(qū)

partition par_02 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace TEMP01,

--2011年第三個(gè)季度為part_03分區(qū)

partition par_03 values less than(to_date('2011-10-01','yyyy-mm-dd')) tablespace TEMP01,

--2011年第四個(gè)季度為part_04分區(qū)

partition par_04 values less than(to_date('2012-01-01','yyyy-mm-dd')) tablespace TEMP01

);

2.創(chuàng)建散列分區(qū)

3.組合分區(qū)：

4.interval 分區(qū)

三.創(chuàng)建索引分區(qū)

索引分區(qū)分為本地索引分區(qū)和全局索引分區(qū)，全局索引不反應(yīng)基礎(chǔ)表的結(jié)構(gòu)，要分區(qū)只能進(jìn)行范圍分區(qū)。

創(chuàng)建索引分區(qū)要參照表分區(qū)

四.分區(qū)技術(shù)簡介

優(yōu)點(diǎn)：

1.減少維護(hù)工作量

2.增強(qiáng)數(shù)據(jù)的可用性

3.均衡I/O,提升性能

4.提高查詢速度

5.分區(qū)對(duì)用戶保持透明，用戶感覺不到分區(qū)的存在。

五,管理表分區(qū)

1.添加表分區(qū)

ALTER TABLE...ALTER PARATITION

2.合并表分區(qū)

3.刪除分區(qū)

ALTER TABLE...DROP PARTITION

刪除分區(qū)時(shí)，里面的數(shù)據(jù)也會(huì)被刪除。

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

create table sales--創(chuàng)建一個(gè)銷售記錄表

(

id number primary key,--記錄編號(hào)

goodsname varchar2(10),--商品名

saledate date--銷售日期

)

partition by range(saledate)--按照日期分區(qū)

(

--第一季度數(shù)據(jù)

partition part_sea1 values less than(to_date('2011-04-01','yyyy-mm-dd')) tablespace tbsp_1,

--第二季度數(shù)據(jù)

partition part_sea2 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace tbsp_2,

--第三季度數(shù)據(jù)

partition part_sea3 values less than(to_date('2011-10-01','yyyy-mm-dd')) tablespace tbsp_1,

--第四季度數(shù)據(jù)

partition part_sea4 values less than(to_date('2012-01-01','yyyy-mm-dd')) tablespace tbsp_2

);

--創(chuàng)建局部索引

create index index_3_4 on sales(saledate)

local(

partition part_seal tablespace tbsp_1,

partition part_sea2 tablespace tbsp_2,

partition part_sea3 tablespace tbsp_1,

partition part_sea4 tablespace tbsp_2

);

--并入分區(qū)

alter table sales merge partitions part_sea3,part_sea4 into partition part_sea4;

--重建局部索引

alter table sales modify partition part_sea4 rebuild unusable local indexes;

六.管理索引分區(qū)

刪除索引：DROP PARTITION

重建分區(qū)：REBUILT PARTITION

更名索引分區(qū)：RENAME PARTITION

分割索引分區(qū)：SPLIT PARTITION

oracle如何按照mod建分區(qū)

根據(jù)數(shù)據(jù)表字段值的范圍進(jìn)行分區(qū)。

創(chuàng)建完分區(qū)表后向表中添加一些數(shù)據(jù)，declarenamevarchar2(10)。fractionnumber(5)。gradenumber(5)。inumber(8)：=1。begin。foriin1。100000LOOP。當(dāng)我們的查詢語句不指定分區(qū)的時(shí)候，如果分區(qū)字段出現(xiàn)在where條件之后，Oracle會(huì)自動(dòng)根據(jù)字段值的范圍掃描響應(yīng)的分區(qū)。

oracle數(shù)據(jù)庫怎么建分區(qū)

一般語法：

create?table?t_test?(

pk_id?number(30)?not?null,

add_date_time??DATE,

constraintPK_T_TEST?primary?key?(pk_id)

)

PARTITION?BY?RANGE?(add_date_time)

(

PARTITIONt_test_2013_less?VALUES?LESS?THAN?(TO_DATE('2013-01-01?00:00:00','yyyy-mm-ddhh24:mi:ss'))?TABLESPACE?TS_MISPS,

PARTITIONt_test_2013?VALUES?LESS?THAN?(TO_DATE('2014-01-01?00:00:00','yyyy-mm-ddhh24:mi:ss'))?TABLESPACE?TS_MISPS,

PARTITION?t_test_2014VALUES?LESS?THAN?(TO_DATE('2015-01-01?00:00:00','yyyy-mm-dd?hh24:mi:ss'))TABLESPACE?TS_MISPS

);

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

在大型的企業(yè)應(yīng)用或企業(yè)級(jí)的數(shù)據(jù)庫應(yīng)用中 要處理的數(shù)據(jù)量通?？梢赃_(dá)到幾十到幾百GB 有的甚至可以到TB級(jí) 雖然存儲(chǔ)介質(zhì)和數(shù)據(jù)處理技術(shù)的發(fā)展也很快 但是仍然不能滿足用戶的需求 為了使用戶的大量的數(shù)據(jù)在讀寫操作和查詢中速度更快 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ū)映射到磁盤以平衡I/O 改善整個(gè)系統(tǒng)性能

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

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

·范圍分區(qū)

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

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

下面將以實(shí)例的方式分別對(duì)這三種分區(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ū)就是對(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 該表將來可能有千萬級(jí)的數(shù)據(jù)記錄數(shù) 要求在建該表的時(shí)候使用分區(qū)表 這時(shí)候我們可以使用序號(hào)分區(qū)三個(gè)區(qū) 每個(gè)區(qū)中預(yù)計(jì)存儲(chǔ)三千萬的數(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在三千萬以下的記錄將存儲(chǔ)在第一個(gè)表空間dinya_space 中 分區(qū)名為:par_ 在三千萬到六千萬之間的記錄存儲(chǔ)在第二個(gè)表空間

dinya_space 中 分區(qū)名為 par_ 而交易ID在六千萬以上的記錄存儲(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)和交易日期來分區(qū)的分區(qū)表 每次插入數(shù)據(jù)的時(shí)候 系統(tǒng)將根據(jù)指定的字段的值來自動(dòng)將記錄存儲(chǔ)到制定的分區(qū)（表空間）中

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

by range ( transaction_id transaction_date)

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

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

散列分區(qū)為通過指定分區(qū)編號(hào)來均勻分布數(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ù)散列地存儲(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ū)來測試分區(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_ 上

查詢分區(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í)間范圍存儲(chǔ)在不同的分區(qū)中 這里是指定了分區(qū)的查詢 當(dāng)然也可以不指定分區(qū) 直接執(zhí)行select * from dinya_test查詢?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ù)查詢的記錄不在該分區(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為 而查詢?nèi)?交易ID為 的記錄在第三個(gè)分區(qū)中 這樣該條語句將不會(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

看查詢的執(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ū)為例說明增加 合并 刪除分區(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ū)后 查詢?cè)摫淼臄?shù)據(jù)時(shí)顯示 該分區(qū)中的數(shù)據(jù)已全部丟失 所以執(zhí)行刪除分區(qū)動(dòng)作時(shí)要慎重 確保先備份數(shù)據(jù)后再執(zhí)行 或?qū)⒎謪^(qū)合并

總結(jié)

lishixinzhi/Article/program/Oracle/201311/17329

oracle11g自動(dòng)分區(qū)

在Oracle10g中，沒有定義間隔分區(qū)，只能通過范圍分區(qū)實(shí)現(xiàn)間隔分區(qū)功能，如果要實(shí)現(xiàn)自動(dòng)創(chuàng)建分區(qū)，只能通過創(chuàng)建JOB或者scheduler來實(shí)現(xiàn)；而在11g中，Oracle直接提供了間隔分區(qū)功能，大大簡化了間隔分區(qū)的實(shí)現(xiàn)。

----注：oracle11g雖然可以自動(dòng)分區(qū)，但是分區(qū)的名字不能自定義，對(duì)于需要定時(shí)刪除分區(qū)時(shí)沒法處理，不如通過時(shí)間范圍來手工分區(qū)。詳見

create table HIP_LOG_NODE_Part

(

ID?????????????????? VARCHAR2(32)???????? not null,

RECORD_TIME????????? DATE

)tablespace TB_HIP_LOG_NODE

PARTITION BY RANGE (RECORD_TIME) interval (numtoyminterval(1, 'month'))

STORE IN (TB_HIP_LOG_NODE)

(

partition hip_log_node_partition values less than (to_date('2019-08-01 00:00','yyyy-MM-dd HH24:mi')) tablespace TB_HIP_LOG_NODE

);

1、Oracle11g有間隔分區(qū)功能，對(duì)于使用Range分區(qū)的可以按年，月，日來自動(dòng)生成分區(qū)。

2、2019-08-01前的數(shù)據(jù)（包含8月份的數(shù)據(jù)）會(huì)放入hip_log_node_partition?分區(qū)，8月1日后的數(shù)據(jù)每月只要有數(shù)據(jù)，就會(huì)自動(dòng)創(chuàng)建一個(gè)分區(qū)。也就是從9月開始，開始新建分區(qū)。

3、interval函數(shù)--將數(shù)值按標(biāo)準(zhǔn)換算為日期

numtodsinterval、numtodsinterval函數(shù),將數(shù)字轉(zhuǎn)成年月，時(shí)分秒

詳見：

4、查看表分區(qū) select table_name,partition_name from user_tab_partitions where table_name='INTERVAL_SALES';

5、插入數(shù)據(jù)再次查看分區(qū)，詳見：

6、修改分區(qū)、合并分區(qū)、拆分分區(qū)，詳見 ：

7、創(chuàng)建索引（分區(qū)索引、全局索引） :

非分區(qū)字段創(chuàng)建主鍵，則創(chuàng)建主鍵local索引時(shí)必須加上分區(qū)字段

ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (主鍵字段,分區(qū)字段) USING INDEX LOCAL;

8、oracle 10g創(chuàng)建表分區(qū)

9、刪除

1.不保留，直接刪除：

alter table table_name drop/truncate partition partition_name;

具體用drop還是truncate，得你自己衡量，drop的話原來的分區(qū)和數(shù)據(jù)直接就沒有了，truncate的話，只是數(shù)據(jù)沒有了，分區(qū)還在。

分享名稱：oracle如何分區(qū)功能 oracle分區(qū)是什么意思
文章起源：http://jinyejixie.com/article20/hpccco.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供移動(dòng)網(wǎng)站建設(shè)、企業(yè)建站、動(dòng)態(tài)網(wǎng)站、企業(yè)網(wǎng)站制作、虛擬主機(jī)、ChatGPT

廣告

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