oracle 之全文索引表的分割槽交換案例
全文索引表的分割槽交換案例第一步 建立分割槽表
SQL> conn scott/qilin
已連線。
SQL> create table t1(
2 object_id int,
3 object_name varchar2(60),
4 created date
5 )
6 partition by range(created)
7 (
8 partition p2009 values less than(to_date('2010-01-01','yyyy-mm-dd')),
9 partition p2010 values less than(to_date('2011-01-01','yyyy-mm-dd')),
10 partition pmax values less than(maxvalue)
11 );
表已建立。
第二步 在表上建立全文索引(由於是分割槽表，通常全文索引也要建立成分割槽索引，這樣有利於資料的載入和解除安裝)
SQL> create index t1_idx on t1(object_name) indextype is ctxsys.context local;
索引已建立。（建立索引的速度比較慢）
第三步 建立臨時表
SQL> create table t1_temp as select * from t1;
表已建立。
建立一個和T1表結構完全相同的臨時表T1_TEMP，只是它不需要是分割槽表，臨時表上暫時不需要建立索引。
使用臨時表的目的在於，表的資料載入和索引的建立都不會對原表產生任何影響，而分割槽交換又是一個非常快的過程（實際上資料位置並沒有發生改變），因此以這種方式載入資料變得越來越流行。
第三步 載入資料
A 控制檔案資訊 2009.ctl
OPTIONS ( DIRECT=TRUE)
LOAD DATA
INFILE "E:\2009.txt"
BADFILE "E:\2009.bad"
DISCARDFILE "E:\2009.dsc"
APPEND INTO TABLE T1_TEMP
(object_id TERMINATED BY WHITESPACE,
object_name TERMINATED BY WHITESPACE,
created DATE "YYYY-MM-DD HH24:MI:SS"TERMINATED BY WHITESPACE)
B 資料檔案內容 2009.txt
20 ICOL$ 2009-5-17 19:21:15
27 I_PROXY_ROLE_DATA$_2 2009-5-17 19:21:15
15 UNDO$ 2009-5-17 19:21:15
29 C_COBJ# 2009-5-17 19:21:15
3 I_OBJ# 2009-5-17 19:21:15
25 PROXY_ROLE_DATA$ 2009-5-17 19:21:15
39 I_IND1 2009-5-17 19:21:15
26 I_PROXY_ROLE_DATA$_1 2009-5-17 19:21:15
17 FILE$ 2009-5-17 19:21:15
13 UET$ 2009-5-17 19:21:15
9 I_FILE#_BLOCK# 2009-5-17 19:21:15
38 I_OBJ3 2009-5-17 19:21:15
7 I_TS# 2009-5-17 19:21:15
19 IND$ 2009-5-17 19:21:15
14 SEG$ 2009-5-17 19:21:15
6 C_TS# 2009-5-17 19:21:15
21 COL$ 2009-5-17 19:21:15
35 I_UNDO2 2009-5-17 19:21:15
5 CLU$ 2009-5-17 19:21:15
23 PROXY_DATA$ 2009-5-17 19:21:15
24 I_PROXY_DATA$ 2009-5-17 19:21:15
36 I_OBJ1 2009-5-17 19:21:15
37 I_OBJ2 2009-5-17 19:21:15
16 TS$ 2009-5-17 19:21:15
8 C_FILE#_BLOCK# 2009-5-17 19:21:15
10 C_USER# 2009-5-17 19:21:15
34 I_UNDO1 2009-5-17 19:21:15
12 FET$ 2009-5-17 19:21:15
33 I_TAB1 2009-5-17 19:21:15
32 CCOL$ 2009-5-17 19:21:15
22 USER$ 2009-5-17 19:21:15
30 I_COBJ# 2009-5-17 19:21:15
18 OBJ$ 2009-5-17 19:21:15
2 C_OBJ# 2009-5-17 19:21:15
4 TAB$ 2009-5-17 19:21:15
C C:\Users\Administrator>sqlldr scott/qilin control=E:\2009.ctl
SQL*Loader: Release 11.2.0.1.0 - Production on 星期三 9月 5 11:19:21 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
載入完成 - 邏輯記錄計數 35。
D SQL> insert into t1_temp(object_id,object_name,created) select object_id,object_
name,created from user_objects;
已建立111行。
SQL> select count(*) from t1_temp;
COUNT(*)
----------
146
E SQL> conn /as sysdba
已連線。
SQL> insert into scott.t1_temp(object_id,object_name,created) select object_id,o
bject_name,created from dba_objects;
已建立72734行。
F SQL> conn scott/qilin
已連線。
SQL> select count(*) from t1_temp;
COUNT(*)
----------
72880
SQL> set linesize 150;
SQL> set pagesize 1000;
SQL> col object_name for a20;
SQL> select * from t1_temp where rownum<20;
OBJECT_ID OBJECT_NAME CREATED
---------- -------------------- --------------
20 ICOL$ 17-5月 -09
27 I_PROXY_ROLE_DATA$_2 17-5月 -09
15 UNDO$ 17-5月 -09
29 C_COBJ# 17-5月 -09
3 I_OBJ# 17-5月 -09
25 PROXY_ROLE_DATA$ 17-5月 -09
39 I_IND1 17-5月 -09
26 I_PROXY_ROLE_DATA$_1 17-5月 -09
17 FILE$ 17-5月 -09
13 UET$ 17-5月 -09
9 I_FILE#_BLOCK# 17-5月 -09
38 I_OBJ3 17-5月 -09
7 I_TS# 17-5月 -09
19 IND$ 17-5月 -09
14 SEG$ 17-5月 -09
6 C_TS# 17-5月 -09
21 COL$ 17-5月 -09
35 I_UNDO2 17-5月 -09
5 CLU$ 17-5月 -09
已選擇19行。
SQL> select count(*) from t1;
COUNT(*)
----------
0
第四步 在臨時表上建立全文索引，這樣可以直接把表資料和索引資料同時交換到原表中，就不需要再在原表中建立索引了。
SQL> create index t1_temp_idx on t1_temp(object_name) indextype is ctxsys.context;
索引已建立。
第五步 查詢相關表的資訊記錄
SQL> col segment_name for a30;
SQL> select segment_name,bytes from user_segments where segment_type='TABLE' and segment_name like 'DR%' order by 1;
SEGMENT_NAME BYTES
------------------------------ ----------
DR#T1_IDX0001$I 65536
DR#T1_IDX0001$R 65536
DR#T1_IDX0002$I 65536
DR#T1_IDX0002$R 65536
DR#T1_IDX0003$I 65536
DR#T1_IDX0003$R 65536
DR$T1_TEMP_IDX$I 5242880
DR$T1_TEMP_IDX$R 65536
已選擇8行。
它們都是T1表中每個分割槽上全文索引的基表，由於T1表是分割槽表，所以全文索引在每個分割槽上建立一套全文索引基表，但是從這個檢視裡我們無法看出全文索引基表屬於哪個分割槽，只能看出一種分割槽順序（001,002,…）。
SQL> select index_name,table_name from user_indexes where table_name in('T1_TEMP','T1');
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
T1_TEMP_IDX T1_TEMP
T1_IDX T1
以上的資訊都說明，現在的資料和索引資訊都儲存在臨時表上，T1表目前還是一個空表。
第六步 進行資料交換(將資料和索引交換到T1表的p2009分割槽中)
SQL> alter table t1 exchange partition p2009 with table t1_temp including indexes without validation;
表已更改。
將T1表的分割槽p2009和T1_TEMP臨時表進行資料交換，交換的資料同時包含索引資訊。
withoutvalidation的意思是，不對錶中的資料是否和分割槽對應進行驗證，因為驗證工作是需要額外開銷的。通常來講，在程式生成待載入的資料檔案的過程中，已經能夠保證這些資料屬於某個分割槽了。
SQL> select count(*) from t1_temp;
COUNT(*)
----------
0
SQL> select count(*) from t1 partition(p2009);
COUNT(*)
----------
72880
T1表的p2009分割槽中的資料為72880，資料已經成功交換到T1表中。
SQL> select segment_name,bytes from user_segments where segment_type='TABLE' and
segment_name like 'DR%' order by 1;
SEGMENT_NAME BYTES
------------------------------ ----------
DR#T1_IDX0001$I 5242880
DR#T1_IDX0001$R 65536
DR#T1_IDX0002$I 65536
DR#T1_IDX0002$R 65536
DR#T1_IDX0003$I 65536
DR#T1_IDX0003$R 65536
DR$T1_TEMP_IDX$I 65536
DR$T1_TEMP_IDX$R 65536
已選擇8行。
全文索引基表的資料已經變更到了T1表的第一個分割槽（也就是P2009）中了，而臨時表的全文索引資料為空。
不論是資料交換還是索引交換，實際上都沒有發生資料移動，只不過資料段的名稱發生了改變。
所以通過分割槽交換的方式，即使資料量非常大，交換也基本上是一個非常迅速的過程。
第七步 使用同樣的方法（進行資料交換(將資料和索引交換到T1表的p2010分割槽中)
SQL> insert into scott.t1_temp(object_id,object_name,created) select object_id,o
bject_name,created from dba_objects;
已建立72744行。
C:\Users\Administrator>sqlldr scott/qilin control=E:\2009.ctl
SQL*Loader: Release 11.2.0.1.0 - Production on 星期三 9月 5 11:50:19 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
載入完成 - 邏輯記錄計數 35。
SQL> select count(*) from t1_temp;
COUNT(*)
----------
72779
SQL> select count(*) from t1 partition(p2010);
COUNT(*)
----------
0
SQL> select segment_name,bytes from user_segments where segment_type='TABLE' and
segment_name like 'DR%' order by 1;
SEGMENT_NAME BYTES
------------------------------ ----------
DR#T1_IDX0001$I 5242880
DR#T1_IDX0001$R 65536
DR#T1_IDX0002$I 65536
DR#T1_IDX0002$R 65536
DR#T1_IDX0003$I 65536
DR#T1_IDX0003$R 65536
DR$T1_TEMP_IDX$I 65536
DR$T1_TEMP_IDX$R 65536
已選擇8行。
現在資料已經載入到了T1_TEMP臨時表中，可是臨時表的全文索引基表看起來並沒有資料。原因是，這種方式建立的全文索引並不是事務型別的，也就是說，索引資料並不會隨著表資料的修改而修改。
使用下面的方式來手工同步索引資訊。
SQL> alter index t1_temp_idx rebuild parameters('sync');
索引已更改。
SQL> select segment_name,bytes from user_segments where segment_type='TABLE' and
segment_name like 'DR%' order by 1;
SEGMENT_NAME BYTES
------------------------------ ----------
DR#T1_IDX0001$I 5242880
DR#T1_IDX0001$R 65536
DR#T1_IDX0002$I 65536
DR#T1_IDX0002$R 65536
DR#T1_IDX0003$I 65536
DR#T1_IDX0003$R 65536
DR$T1_TEMP_IDX$I 5242880
DR$T1_TEMP_IDX$R 65536
已選擇8行。
此時全文索引資訊已經更新了。
將資料和索引交換到T1表的P2010分割槽中。
SQL> alter table t1 exchange partition p2010 with table t1_temp including indexes without validation;
表已更改。
SQL> select segment_name,bytes from user_segments where segment_type='TABLE' and
segment_name like 'DR%' order by 1;
SEGMENT_NAME BYTES
------------------------------ ----------
DR#T1_IDX0001$I 5242880
DR#T1_IDX0001$R 65536
DR#T1_IDX0002$I 5242880
DR#T1_IDX0002$R 65536
DR#T1_IDX0003$I 65536
DR#T1_IDX0003$R 65536
DR$T1_TEMP_IDX$I 65536
DR$T1_TEMP_IDX$R 65536
已選擇8行。
已經有兩個分割槽索引中的索引資料被交換進來了
SQL> select count(*) from t1 partition(p2010);
COUNT(*)
----------
72779
第八步 把最後一個分割槽的資料載入進T1_TEMP臨時表中，並交換到T1表的PMAX分割槽中。
SQL> insert into scott.t1_temp(object_id,object_name,created) select object_id,o
bject_name,created from dba_objects;
已建立72744行。
SQL> commit;
提交完成。
SQL> select count(*) from t1_temp;
COUNT(*)
----------
72744
SQL> select segment_name,bytes from user_segments where segment_type='TABLE' and
segment_name like 'DR%' order by 1;
SEGMENT_NAME BYTES
------------------------------ ----------
DR#T1_IDX0001$I 5242880
DR#T1_IDX0001$R 65536
DR#T1_IDX0002$I 5242880
DR#T1_IDX0002$R 65536
DR#T1_IDX0003$I 65536
DR#T1_IDX0003$R 65536
DR$T1_TEMP_IDX$I 65536
DR$T1_TEMP_IDX$R 65536
已選擇8行。
現在資料已經載入到了T1_TEMP臨時表中，可是臨時表的全文索引基表看起來並沒有資料。原因是，這種方式建立的全文索引並不是事務型別的，也就是說，索引資料並不會隨著表資料的修改而修改。
使用下面的方式來手工同步索引資訊。
SQL> alter index t1_temp_idx rebuild parameters('sync');
SQL> select segment_name,bytes from user_segments where segment_type='TABLE' and segment_name like 'DR%' order by 1;
SEGMENT_NAME BYTES
------------------------------ ----------
DR#T1_IDX0001$I 5242880
DR#T1_IDX0001$R 65536
DR#T1_IDX0002$I 5242880
DR#T1_IDX0002$R 65536
DR#T1_IDX0003$I 65536
DR#T1_IDX0003$R 65536
DR$T1_TEMP_IDX$I 5242880
DR$T1_TEMP_IDX$R 65536
已選擇8行。
此時全文索引資訊已經更新了
將資料和索引交換到T1表的PMAX分割槽中。
SQL> alter table t1 exchange partition pmax with table t1_temp including indexes without validation;
表已更改。
SQL> select segment_name,bytes from user_segments where segment_type='TABLE' and segment_name like 'DR%' order by 1;
SEGMENT_NAME BYTES
------------------------------ ----------
DR#T1_IDX0001$I 5242880
DR#T1_IDX0001$R 65536
DR#T1_IDX0002$I 5242880
DR#T1_IDX0002$R 65536
DR#T1_IDX0003$I 5242880
DR#T1_IDX0003$R 65536
DR$T1_TEMP_IDX$I 65536
DR$T1_TEMP_IDX$R 65536
已選擇8行。
第九步 查詢核對相應分割槽表上的資訊
SQL> select count(*) from t1 partition(p2009);
COUNT(*)
----------
72880
SQL> select count(*) from t1 partition(p2010);
COUNT(*)
----------
72779
SQL> select count(*) from t1 partition(pmax);
COUNT(*)
----------
72744
SQL> select count(*) from t1_temp;
COUNT(*)
----------
0
這樣，我們用一個臨時表作為中間表，通過分割槽交換的方式成功將資料交換到T1表中。
這樣做的好處在於，把資料載入和索引建立都放在一個臨時表中進行，這兩個階段通常是比較耗資源和耗時的，並且會在表上建立鎖，如果在原表上操作，可能會引起一
些阻塞現象發生。當這些事情全部放在一個不相干的臨時表中做完之後，剩下的工作就是一個非常短暫的資料交換操作了，這樣就把資料載入和索引建立對原表造成的影響減少到了最小。
在海量資料的資料庫設計中，使用全文索引應特別注意下面兩點：
●全文索引佔用的空間；
●全文索引和DML操作。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/20976446/viewspace-742715/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle之分割槽交換Oracle
- 全面學習分割槽表及分割槽索引(10)--交換分割槽索引
- oracle分割槽表和分割槽表exchangeOracle
- oracle分割槽交換（exchange）技術Oracle
- Oracle Vs MsSQL 之交換分割槽OracleSQL
- 交換分割槽之without validation
- ［引用分割槽表］Oracle 11g新特性之引用分割槽表Oracle
- oracle分割槽表和非分割槽表exchangeOracle
- Oracle分割槽表及分割槽索引Oracle索引
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- Oracle 表分割槽Oracle
- oracle分割槽表Oracle
- oracle表分割槽Oracle
- Oracle 分割槽表Oracle
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- 簡單ORACLE分割槽表、分割槽索引Oracle索引
- Oracle 分割槽表的建立Oracle
- Oracle分割槽表的使用Oracle
- Oracle分割槽表的管理Oracle
- 分割槽表索引實踐案例索引
- Oracle 建立分割槽表Oracle
- ORACLE分割槽表管理Oracle
- Oracle 12.2之後ALTER TABLE .. MODIFY轉換非分割槽表為分割槽表Oracle
- 【實驗】【PARTITION】交換分割槽時分割槽表有主鍵目標表亦需有主鍵
- linux交換分割槽Linux
- Oracle分割槽表基礎運維-06分割槽表索引Oracle運維索引
- oracle分割槽表的維護Oracle
- Oracle中分割槽表的使用Oracle
- Oracle帶區域性分割槽索引的分割槽表刪除舊分割槽新增新分割槽Oracle索引
- Oracle 12C 新特性之表分割槽或子分割槽的線上遷移Oracle
- 深入學習Oracle分割槽表及分割槽索引Oracle索引
- 全面認識oracle分割槽表及分割槽索引Oracle索引
- Oracle分割槽表基礎運維-07增加分割槽(3列表分割槽)Oracle運維
- Oracle分割槽表基礎運維-01分割槽表分類Oracle運維
- Oracle分割槽表基礎運維-04列表分割槽Oracle運維
- ORACLE分割槽表梳理系列Oracle
- Oracle分割槽表遷移Oracle
- oracle 分割槽表詳解Oracle