ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 之全文索引表的分区交换案例

oracle 之全文索引表的分区交换案例

原创 Linux操作系统 作者:liqilin0429 时间:2012-09-05 15:16:15 0 删除 编辑

全文索引表的分区交换案例第一步 创建分区表
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之分区交换
下一篇: rac维护基本命令
请登录后发表评论 登录
全部评论

注册时间:2010-08-13

  • 博文量
    100
  • 访问量
    197778