全文索引表的分区交换案例第一步 创建分区表
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/20976446/viewspace-742715/