關(guān)于分區(qū)表和分區(qū)索引(About Partitioned Tables and Indexes)對(duì)于10gR2而言，基本上可以分成幾類：
- Range(范圍)分區(qū)
- Hash(哈希)分區(qū)
- List(列表)分區(qū)
- 以及組合分區(qū)：Range-Hash,Range-List。
對(duì)于表而言(常規(guī)意義上的堆組織表)，上述分區(qū)形式都可以應(yīng)用(甚至可以對(duì)某個(gè)分區(qū)指定compress屬性)，只不過分區(qū)依賴列不能是lob,long之類數(shù)據(jù)類型，每個(gè)表的分區(qū)或子分區(qū)數(shù)的總數(shù)不能超過1023個(gè)。
對(duì)于索引組織表，只能夠支持普通分區(qū)方式，不支持組合分區(qū)，常規(guī)表的限制對(duì)于索引組織表同樣有效，除此之外呢，還有一些其實(shí)的限制，比如要求索引組織表的分區(qū)依賴列必須是主鍵才可以等。
注：本篇所有示例僅針對(duì)常規(guī)表，即堆組織表!
對(duì)于索引，需要區(qū)分創(chuàng)建的是全局索引，或本地索引：
l 全局索引(global index)：即可以分區(qū)，也可以不分區(qū)。即可以建range分區(qū)，也可以建hash分區(qū)，即可建于分區(qū)表，又可創(chuàng)建于非分區(qū)表上，就是說，全局索引是完全獨(dú)立的，因此它也需要我們更多的維護(hù)操作。
l 本地索引(local index)：其分區(qū)形式與表的分區(qū)完全相同，依賴列相同，存儲(chǔ)屬性也相同。對(duì)于本地索引，其索引分區(qū)的維護(hù)自動(dòng)進(jìn)行，就是說你add/drop/split/truncate表的分區(qū)時(shí)，本地索引會(huì)自動(dòng)維護(hù)其索引分區(qū)。
Oracle建議如果單個(gè)表超過2G就最好對(duì)其進(jìn)行分區(qū)，對(duì)于大表創(chuàng)建分區(qū)的好處是顯而易見的，這里不多論述why，而將重點(diǎn)放在when以及how。
WHEN
一、When使用Range分區(qū)
Range分區(qū)呢是應(yīng)用范圍比較廣的表分區(qū)方式，它是以列的值的范圍來做為分區(qū)的劃分條件，將記錄存放到列值所在的range分區(qū)中，比如按照時(shí)間劃分，2008年1季度的數(shù)據(jù)放到a分區(qū)，08年2季度的數(shù)據(jù)放到b分區(qū)，因此在創(chuàng)建的時(shí)候呢，需要你指定基于的列，以及分區(qū)的范圍值，如果某些記錄暫無法預(yù)測(cè)范圍，可以創(chuàng)建maxvalue分區(qū)，所有不在指定范圍內(nèi)的記錄都會(huì)被存儲(chǔ)到maxvalue所在分區(qū)中，并且支持指定多列做為依賴列，后面在講how的時(shí)候會(huì)詳細(xì)談到。
二、When使用Hash分區(qū)
通常呢，對(duì)于那些無法有效劃分范圍的表，可以使用hash分區(qū)，這樣對(duì)于提高性能還是會(huì)有一定的幫助。hash分區(qū)會(huì)將表中的數(shù)據(jù)平均分配到你指定的幾個(gè)分區(qū)中，列所在分區(qū)是依據(jù)分區(qū)列的hash值自動(dòng)分配，因此你并不能控制也不知道哪條記錄會(huì)被放到哪個(gè)分區(qū)中，hash分區(qū)也可以支持多個(gè)依賴列。
三、When使用List分區(qū)
List分區(qū)與range分區(qū)和hash分區(qū)都有類似之處，該分區(qū)與range分區(qū)類似的是也需要你指定列的值，但這又不同與range分區(qū)的范圍式列值---其分區(qū)值必須明確指定，也不同與hash分區(qū)---通過明確指定分區(qū)值，你能控制記錄存儲(chǔ)在哪個(gè)分區(qū)。它的分區(qū)列只能有一個(gè)，而不能像range或者h(yuǎn)ash分區(qū)那樣同時(shí)指定多個(gè)列做為分區(qū)依賴列，不過呢，它的單個(gè)分區(qū)對(duì)應(yīng)值可以是多個(gè)。
你在分區(qū)時(shí)必須確定分區(qū)列可能存在的值，一旦插入的列值不在分區(qū)范圍內(nèi)，則插入/更新就會(huì)失敗，因此通常建議使用list分區(qū)時(shí)，要?jiǎng)?chuàng)建一個(gè)default分區(qū)存儲(chǔ)那些不在指定范圍內(nèi)的記錄，類似range分區(qū)中的maxvalue分區(qū)。
四、When使用組合分區(qū)
如果某表按照某列分區(qū)之后，仍然較大，或者是一些其它的需求，還可以通過分區(qū)內(nèi)再建子分區(qū)的方式將分區(qū)再分區(qū)，即組合分區(qū)的方式。
組合分區(qū)呢在10g中有兩種：range-hash，range-list。注意順序喲，根分區(qū)只能是range分區(qū)，子分區(qū)可以是hash分區(qū)或list分區(qū)。
提示：11g在組合分區(qū)功能這塊有所增強(qiáng)，又推出了range-range,list-range,list-list,list-hash，這就相當(dāng)于除hash外三種分區(qū)方式的笛卡爾形式都有了。為什么會(huì)沒有hash做為根分區(qū)的組合分區(qū)形式呢，再仔細(xì)回味一下第二點(diǎn)，你一定能夠想明白~~。
一、如何創(chuàng)建
如果想對(duì)某個(gè)表做分區(qū)，必須在創(chuàng)建表時(shí)就指定分區(qū)，我們可以對(duì)一個(gè)包含分區(qū)的表中的分區(qū)做修改，但不能直接將一個(gè)未分區(qū)的表修改成分區(qū)表(起碼在10g是不行的，當(dāng)然你可能會(huì)說，可以通過在線重定義的方式，但是這不是直接喲，這也是借助臨時(shí)表間接實(shí)現(xiàn)的)。
創(chuàng)建表或索引的語法就不說了，大家肯定比我還熟悉，而想在建表(索引)同時(shí)指定分區(qū)也非常容易，只需要把創(chuàng)建分區(qū)的子句放到";"前就行啦，同時(shí)需要注意表的row movement屬性，它用來控制是否允許修改列值所造成的記錄移動(dòng)至其它分區(qū)存儲(chǔ)，有enable|disable兩種狀態(tài)，默認(rèn)是disable row movement，當(dāng)disable時(shí)，如果記錄要被更新至其它分區(qū)，則更新語句會(huì)報(bào)錯(cuò)。
下面分別演示不同分區(qū)方式的表和索引的創(chuàng)建：
1、創(chuàng)建range分區(qū)
語法如下，圖：[range_partitioning.gif]
需要我們指定的有：
l column:分區(qū)依賴列(如果是多個(gè)，以逗號(hào)分隔);
l partition:分區(qū)名稱;
l values less than:后跟分區(qū)范圍值(如果依賴列有多個(gè)，范圍對(duì)應(yīng)值也應(yīng)是多個(gè)，中間以逗號(hào)分隔);
l tablespace_clause:分區(qū)的存儲(chǔ)屬性，例如所在表空間等屬性(可為空)，默認(rèn)繼承基表所在表空間的屬性。
① 創(chuàng)建一個(gè)標(biāo)準(zhǔn)的range分區(qū)表：
JSSWEB> create table t_partition_range (id number,name varchar2(50))
2 partition by range(id)(
3 partition t_range_p1 values less than (10) tablespace tbspart01,
4 partition t_range_p2 values less than (20) tablespace tbspart02,
5 partition t_range_p3 values less than (30) tablespace tbspart03,
6 partition t_range_pmax values less than (maxvalue) tablespace tbspart04
7 ); |
表已創(chuàng)建。
要查詢創(chuàng)建分區(qū)的信息，可以通過查詢user_part_tables,user_tab_partitions兩個(gè)數(shù)據(jù)字典(索引分區(qū)、組織分區(qū)等信息也有對(duì)應(yīng)的數(shù)據(jù)字典，后續(xù)示例會(huì)逐步提及)。
user_part_tables：記錄分區(qū)的表的信息;
user_tab_partitions：記錄表的分區(qū)的信息。
例如：
| JSSWEB> select table_name,partitioning_type,partition_count
2 From user_part_tables where table_name='T_PARTITION_RANGE';
TABLE_NAME PARTITI PARTITION_COUNT
------------------------------ ------- ---------------
T_PARTITION_RANGE RANGE 4
JSSWEB> select partition_name,high_value,tablespace_name
2 from user_tab_partitions where table_name='T_PARTITION_RANGE'
3 order by partition_position;
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ---------- --------------------
T_RANGE_P1 10 TBSPART01
T_RANGE_P2 20 TBSPART02
T_RANGE_P3 30 TBSPART03
T_RANGE_PMAX MAXVALUE TBSPART04
|
創(chuàng)建global索引range分區(qū)：
JSSWEB> create index idx_parti_range_id on t_partition_range(id)
2 global partition by range(id)(
3 partition i_range_p1 values less than (10) tablespace tbspart01,
4 partition i_range_p2 values less than (40) tablespace tbspart02,
5 partition i_range_pmax values less than (maxvalue) tablespace tbspart03); |
索引已創(chuàng)建。
由上例可以看出，創(chuàng)建global索引的分區(qū)與創(chuàng)建表的分區(qū)語句格式完全相同，而且其分區(qū)形式與索引所在表的分區(qū)形式?jīng)]有關(guān)聯(lián)關(guān)系。
注意：我們這里借助上面的表t_partition_range來演示創(chuàng)建range分區(qū)的global索引，并不表示range分區(qū)的表，只能創(chuàng)建range分區(qū)的global索引，只要你想，也可以為其創(chuàng)建hash分區(qū)的global索引。
查詢索引的分區(qū)信息可以通過user_part_indexes、user_ind_partitions兩個(gè)數(shù)據(jù)字典：
| JSSWEB> select index_name, partitioning_type, partition_count
2 From user_part_indexes
3 where index_name = 'IDX_PARTI_RANGE_ID';
INDEX_NAME PARTITI PARTITION_COUNT
------------------------------ ------- ---------------
IDX_PARTI_RANGE_ID RANGE 3
JSSWEB> select partition_name, high_value, tablespace_name
2 from user_ind_partitions
3 where index_name = 'IDX_PARTI_RANGE_ID'
4 order by partition_position;
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ---------- --------------------
I_RANGE_P1 10 TBSPART01
I_RANGE_P2 40 TBSPART02
I_RANGE_PMAX MAXVALUE TBSPART03
|
③ Local分區(qū)索引的創(chuàng)建最簡(jiǎn)單，例如：
仍然借助t_partition_range表來創(chuàng)建索引
--首先刪除之前創(chuàng)建的global索引
JSSWEB> drop index IDX_PARTI_RANGE_ID;
索引已刪除。
JSSWEB> create index IDX_PARTI_RANGE_ID on T_PARTITION_RANGE(id) local;
索引已創(chuàng)建。
查詢相關(guān)數(shù)據(jù)字典：
| JSSWEB> select index_name, partitioning_type, partition_count
2 From user_part_indexes
3 where index_name = 'IDX_PARTI_RANGE_ID';
INDEX_NAME PARTITI PARTITION_COUNT
------------------------------ ------- ---------------
IDX_PARTI_RANGE_ID RANGE 4
JSSWEB> select partition_name, high_value, tablespace_name
2 from user_ind_partitions
3 where index_name = 'IDX_PARTI_RANGE_ID'
4 order by partition_position;
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
------------------------------ ---------- --------------------
T_RANGE_P1 10 TBSPART01
T_RANGE_P2 20 TBSPART02
T_RANGE_P3 30 TBSPART03
T_RANGE_PMAX MAXVALUE TBSPART04
|
可以看出，local索引的分區(qū)完全繼承表的分區(qū)的屬性，包括分區(qū)類型，分區(qū)的范圍值即不需指定也不能更改，這就是前面說的：local索引的分區(qū)維護(hù)完全依賴于其索引所在表。
不過呢分區(qū)名稱，以及分區(qū)所在表空間等信息是可以自定義的，例如：
SQL> create index IDX_PART_RANGE_ID ON T_PARTITION_RANGE(id) local (
2 partition i_range_p1 tablespace tbspart01,
3 partition i_range_p2 tablespace tbspart01,
4 partition i_range_p3 tablespace tbspart02,
5 partition i_range_pmax tablespace tbspart02
6 ); |
索引已創(chuàng)建。
| SQL> select index_name, partitioning_type, partition_count
2 From user_part_indexes
3 where index_name = 'IDX_PART_RANGE_ID';
INDEX_NAME PARTITI PARTITION_COUNT
------------------------------ ------- ---------------
IDX_PART_RANGE_ID RANGE 4
SQL> select partition_name, high_value, tablespace_name
2 from user_ind_partitions
3 where index_name = 'IDX_PART_RANGE_ID'
4 order by partition_position;
PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
--------------- --------------- --------------------
I_RANGE_P1 10 TBSPART01
I_RANGE_P2 20 TBSPART01
I_RANGE_P3 30 TBSPART02
I_RANGE_PMAX MAXVALUE TBSPART02
|
創(chuàng)建hash分區(qū)
語法如下：[圖:hash_partitioning.gif]
語法看起來比range復(fù)雜，其實(shí)使用起來比range更簡(jiǎn)單，這里需要我們指定的有：
l column:分區(qū)依賴列(支持多個(gè)，中間以逗號(hào)分隔);
l partition:指定分區(qū)，有兩種方式：
n 直接指定分區(qū)名，分區(qū)所在表空間等信息
n 只指定分區(qū)數(shù)量，和可供使用的表空間。
① 創(chuàng)建hash分區(qū)表
JSSWEB> create table t_partition_hash (id number,name varchar2(50))
2 partition by hash(id)(
3 partition t_hash_p1 tablespace tbspart01,
4 partition t_hash_p2 tablespace tbspart02,
5 partition t_hash_p3 tablespace tbspart03); |
表已創(chuàng)建。
要實(shí)現(xiàn)同樣效果，你還可以這樣：
JSSWEB> create table t_partition_hash2 (id number,name varchar2(50))
2 partition by hash(id)
3 partitions 3 store in(tbspart01,tbspart02,tbspart03); |
表已創(chuàng)建。
這就是上面說的，直接指定分區(qū)數(shù)量和可供使用的表空間。
提示：這里分區(qū)數(shù)量和可供使用的表空間數(shù)量之間沒有直接對(duì)應(yīng)關(guān)系。分區(qū)數(shù)并不一定要等于表空間數(shù)。
要查詢表的分區(qū)信息，仍然是通過user_part_tables,user_tab_partitions兩個(gè)數(shù)據(jù)字典，這里不再舉例。
② Global索引hash分區(qū)
Hash分區(qū)索引的子句與hash分區(qū)表的創(chuàng)建子句完全相同，例如：
JSSWEB> create index idx_part_hash_id on t_partition_hash(id)
2 global partition by hash(id)
3 partitions 3 store in(tbspart01,tbspart02,tbspart03); |
索引已創(chuàng)建。
查詢索引的分區(qū)信息也仍是通過user_part_indexes、user_ind_partitions兩個(gè)數(shù)據(jù)字典，不再舉例。
③ 創(chuàng)建Local索引
在前面學(xué)習(xí)range分區(qū)時(shí)，我們已經(jīng)對(duì)Local索引的特性做了非常清晰的概述，因此這里也不再舉例，如有疑問，建議再仔細(xì)復(fù)習(xí)range分區(qū)的相關(guān)示例，如果還有疑問，當(dāng)面問我好了:)
綜上：
Ø 對(duì)于global索引分區(qū)而言，在10g中只能支持range分區(qū)和hash分區(qū)，因此后續(xù)示例中不會(huì)再提及。
Ø 對(duì)于local索引分區(qū)而言，其分區(qū)形式完全依賴于索引所在表的分區(qū)形式，不管從創(chuàng)建語法還是理解難度均無技術(shù)含量，因此后續(xù)也不再提供示例。
Ø 注意，在創(chuàng)建索引時(shí)如果不顯式指定global或local，則默認(rèn)是global。
Ø 注意，在創(chuàng)建global索引時(shí)如果不顯式指定分區(qū)子句，則默認(rèn)不分區(qū)(廢話)。
全面學(xué)習(xí)Oracle分區(qū)表及分區(qū)索引(4)-創(chuàng)建list分區(qū)
創(chuàng)建list分區(qū)
創(chuàng)建語法如下：[圖：list_partitioning.gif]
需要我們指定的有：
l column:分區(qū)依賴列，注意：只能是一個(gè);
l partition:分區(qū)名稱;
l literal:分區(qū)對(duì)應(yīng)值，注意：每個(gè)分區(qū)可以對(duì)應(yīng)多個(gè)值;
l tablespace_clause:分區(qū)的存儲(chǔ)屬性，例如所在表空間等屬性(可為空)，默認(rèn)繼承基表所在表空間的屬性。
創(chuàng)建list分區(qū)表示例：
JSSWEB> create table t_partition_list (id number,name varchar2(50))
2 partition by list(id)(
3 partition t_list_p1 values (1,2,3,4,5,6,7,8,9) tablespace tbspart01,
4 partition t_list_p2 values (10,11,12,13,14,15,16,17,18,19) tablespace tbspart02,
5 partition t_list_p3 values (20,21,22,23,24,25,26,27,28,29) tablespace tbspart03,
6 partition t_list_pd values (default) tablespace tbspart04);
表已創(chuàng)建。
上例能夠?qū)崿F(xiàn)與前面range分區(qū)示例相同的效果，當(dāng)然針對(duì)本示例而言，list分區(qū)顯然不好用啊~~~