Oracle Interval

aaie_ 2018-02-14

展開全文

轉(zhuǎn)載自：http://www./database/201309/244285.html

Oracle Interval-Partition解決Range分區(qū)大難題

《oracle分區(qū)》中講了oracle的幾種分區(qū)，并且對于oracle的典型分區(qū)如Range分區(qū)和List分區(qū)給了示例。

在實際運(yùn)用Range分區(qū)時，遇到了這樣的難題：

?

createtableTMP_LXQ_1

(

 PROPOSALNO            
 VARCHAR2(22),

 STARTDATE             DATE

)

partitionbyrange(STARTDATE)
 (

partitionpart_t01values
 less than(to_date('2008-01-01','yyyy-mm-dd'))
 ,

partitionpart_t02values
 less than(to_date('2009-01-01','yyyy-mm-dd'))
 ,

partitionpart_t03values
 less than(to_date('2010-01-01','yyyy-mm-dd'))
 ,

partitionpart_t04values
 less than(to_date('2011-01-01','yyyy-mm-dd'))
 ,

partitionpart_t05values
 less than(to_date('2012-01-01','yyyy-mm-dd'))
 ,

partitionpart_t06values
 less than(to_date('2013-01-01','yyyy-mm-dd'))
 ,

partitionpart_t07values
 less than(maxvalue)

);

本例中的時間只是到了2013年，然后2013年之后的時間完全放入到了maxvalue中，這確實也是一種解決辦法，即有除去2008-2013年的數(shù)據(jù)時，數(shù)據(jù)庫不會報錯。但是這樣就改變了我們分區(qū)的初衷，分區(qū)是想讓各個部分的數(shù)據(jù)均衡，以加快查詢。

在oracle11g出現(xiàn)之前，實際工作中經(jīng)常會遇到這種情況，而處理這種情況，通常是由DBA或者開發(fā)人員手動進(jìn)行分區(qū)或者直接定義maxvalue。

Oracle 11g的新增特性Interval分區(qū)可以解決這個問題，下面介紹一下Interval分區(qū)。

一、interval分區(qū)

INTERVAL分區(qū)是Oracle11g新增的特性，它是針對Range類型分區(qū)的一種功能拓展。對連續(xù)數(shù)據(jù)類型的Range分區(qū)，如果插入的新數(shù)據(jù)值與當(dāng)前分區(qū)均不匹配，Interval-Partition特性可以實現(xiàn)自動的分區(qū)創(chuàng)建。

示例：

?

createtable
 TMP_LXQ_1

(

proposalnovarchar2(22),

startdatedate

)

partitionby
 range(STARTDATE)

interval(numtoyminterval(1,'year'))(

partitionpart_t01valuesless
 than(to_date('2008-01-01','yyyy-mm-dd'))
 ,

partitionpart_t02valuesless
 than(to_date('2009-01-01','yyyy-mm-dd'))
 ,

partitionpart_t03valuesless
 than(to_date('2010-01-01','yyyy-mm-dd'))
 ,

partitionpart_t04valuesless
 than(to_date('2011-01-01','yyyy-mm-dd'))
 ,

partitionpart_t05valuesless
 than(to_date('2012-01-01','yyyy-mm-dd'))
 ,

partitionpart_t06valuesless
 than(to_date('2013-01-01','yyyy-mm-dd'))

);

如果插入2014年的值，系統(tǒng)會自動的添加一個分區(qū)，分區(qū)范圍為2014-01-01到2014-12-31日。

--如果對分區(qū)名不太爽，則可以自己修改一下：

alter table t_range rename partition SYS_P21 to p_2014_02_01

alter table t_range set STORE IN (tablespace1, tablespace2, tablespace3);
這3個表空間，分區(qū)會循環(huán)分配到這3個表空間。

二、interval分區(qū)和range分區(qū)的轉(zhuǎn)換

ALTERTABLE TMP_LXQ_1 SET INTERVAL (numtoyminterval(1,'year'));

對于INTERVAL分區(qū)表，新增的超過分區(qū)上限的數(shù)據(jù)會自動導(dǎo)致對應(yīng)的INTERVAL分區(qū)被建立。

同樣INTERVAL分區(qū)表可以方便的轉(zhuǎn)化為RANGE分區(qū)表，只需要不輸入INTERVAL的值即可：

ALTER TABLETMP_LXQ_1 SET INTERVAL ();

三、interval分區(qū)的特點

1.由range分區(qū)派生而來

2.以定長寬度創(chuàng)建分區(qū)（比如年、月、具體的數(shù)字（比如100、500等））

3.分區(qū)字段必須是number或date類型

4.必須至少指定一個range分區(qū)（永久分區(qū)）

5.當(dāng)有記錄插入時，系統(tǒng)根據(jù)需要自動創(chuàng)建新的分區(qū)和本地索引

6.已有的范圍分區(qū)可被轉(zhuǎn)換成間隔分區(qū)（通過ALTER TABLE SET INTERVAL選項完成）

7.IntervalPartitioning不支持支持索引組織表

8.在Interval Partitioning表上不能創(chuàng)建domain index

四、interval分區(qū)問題

在oracle自動創(chuàng)建分區(qū)的時候，系統(tǒng)會默認(rèn)指定一個分區(qū)名，系統(tǒng)默認(rèn)創(chuàng)建的分區(qū)名字與我們的分區(qū)命名規(guī)范會有一定的差距，這個問題暫時還不知道有什么解決方法。

備注：

顯示分區(qū)表信息
　　顯示數(shù)據(jù)庫所有分區(qū)表的信息：DBA_PART_TABLES
　　顯示當(dāng)前用戶可訪問的所有分區(qū)表信息：ALL_PART_TABLES
　　顯示當(dāng)前用戶所有分區(qū)表的信息：USER_PART_TABLES
　　顯示表分區(qū)信息 顯示數(shù)據(jù)庫所有分區(qū)表的詳細(xì)分區(qū)信息：DBA_TAB_PARTITIONS
　　顯示當(dāng)前用戶可訪問的所有分區(qū)表的詳細(xì)分區(qū)信息：ALL_TAB_PARTITIONS
　　顯示當(dāng)前用戶所有分區(qū)表的詳細(xì)分區(qū)信息：USER_TAB_PARTITIONS
　　顯示子分區(qū)信息 顯示數(shù)據(jù)庫所有組合分區(qū)表的子分區(qū)信息：DBA_TAB_SUBPARTITIONS
　　顯示當(dāng)前用戶可訪問的所有組合分區(qū)表的子分區(qū)信息：ALL_TAB_SUBPARTITIONS
　　顯示當(dāng)前用戶所有組合分區(qū)表的子分區(qū)信息：USER_TAB_SUBPARTITIONS
　　顯示分區(qū)列 顯示數(shù)據(jù)庫所有分區(qū)表的分區(qū)列信息：DBA_PART_KEY_COLUMNS
　　顯示當(dāng)前用戶可訪問的所有分區(qū)表的分區(qū)列信息：ALL_PART_KEY_COLUMNS
　　顯示當(dāng)前用戶所有分區(qū)表的分區(qū)列信息：USER_PART_KEY_COLUMNS
　　顯示子分區(qū)列 顯示數(shù)據(jù)庫所有分區(qū)表的子分區(qū)列信息：DBA_SUBPART_KEY_COLUMNS
　　顯示當(dāng)前用戶可訪問的所有分區(qū)表的子分區(qū)列信息：ALL_SUBPART_KEY_COLUMNS
　　顯示當(dāng)前用戶所有分區(qū)表的子分區(qū)列信息：USER_SUBPART_KEY_COLUMNS

        查看數(shù)據(jù)庫中所有數(shù)據(jù)文件的名稱位置,所屬表空間,以及大小等等  dba_data_files

　　---------------------------------------------------------------------------------------------------
　　select * from user_tables a where a.partitioned='YES'

如果不想使用以上的方法動態(tài)增加表的分區(qū)，就需要用下面的方式手工增加分區(qū)：

1：增加tablespace

create tablespace 空間_2019
datafile '/oradata/空間_2019.dbf' size 1500M
DEFAULT STORAGE (
initial 64K
next 1M
minextents 1
maxextents unlimited
);

如果不知道如何設(shè)置tablespace存放地址，可以查看select * from dba_data_files; 參照寫個地址

2：增加表分區(qū)

alter table POSITION_GPSONE_YEAR add partition PART_2013 values less than ('2014') tablespace TBSERVER;

表名：POSITION_GPSONE_YEAR

分區(qū)名：PART_2013

分區(qū)字段的值要小于該值：2014

這個表所在的表空間：TBSERVER