演示一个带有全文索引表的分区交换例子

一、实验说明：

      操作系统：rhel 5.4 x86

      数据库：Oracle 11g R2

      实验说明:该实验参照了谭老师的《让Oracle跑的更快2》中的一个案例。

二、在数据库中创建带加载数据的分区表及索引

 ----------创建一个包含3个分区的分区表，分区的字段是一个时间字段，分别存放2011年、2012年和之后的数据。-----
 1 SQL> create table jack_test(id int,name varchar2(60),created date)
 2   2   partition by range(created)
 3   3  (
 4   4   partition p2011 values less than(to_date('2012-01-01','yyyy-mm-dd')),
 5   5   partition p2012 values less than(to_date('2013-01-01','yyyy-mm-dd')),
 6   6   partition pmax values less than (maxvalue)
 7   7  );
 8 
 9 Table created.
 ----------创建全文索引----------------------------------------------------------------------
10 
11 SQL> create index jack_test_ind on jack_test(name) indextype is ctxsys.context local;
12 
13 Index created.

三、在数据库中创建临时表

----------创建一个和jack_test表结构完全相同的临时表jack_test，只是它不是分区表，临时表上暂时不需要创建索引-------
1 SQL> create table jack_temp as select * from jack_test;
2 
3 Table created.

 

四、由程序将待加载的数据写成固定格式的文件

 ---------下面是sqlldr的一个控制文件-------------------------
 1 [oracle@node2 jack]$ vi jack_test_2011.ctl 
 2 
 3 OPTIONS (DIRECT=TRUE)
 4 LOAD DATA
 5 INFILE '/home/oracle/jack/jack_test_2011.dat'
 6 BADFILE '/home/oracle/jack/jack_test_2011.bad'
 7 DISCARDFILE '/home/oracle/jack/jack_test_2011.dsc'
 8 APPEND INTO TABLE jack_temp
 9 (id TERMINATED BY WHITESPACE,
10  name TERMINATED BY WHITESPACE,
11  created DATE "YYYY-MM-DD HH24:MI:SS" TERMINATED BY WHITESPACE)
12 
 ----------下面是需要加载的格式文件中的部分数据--------------------------
13 [oracle@node2 jack]$ more jack_test_2011.dat 
14 13628    index    2011-6-8
15 13629    index    2011-6-8
16 13630    index    2011-6-8
17 13631    index    2011-6-8
18 13632    index    2011-6-8
19 13633    index    2011-6-8
20 13634    index    2011-6-8
21 13635    index    2011-6-8
22 13636    index    2011-6-8
23 13637    index    2011-6-8
24 13638    index    2011-6-8
25 13639    index    2011-6-8
26 13640    index    2011-6-8
27 13641    index    2011-6-8
28 13642    index    2011-6-8
29 13643    index    2011-6-8

 

五、使用SQL*Loader将文件装载入临时文件中

 -------------开始加载数据---------------------------------------------------
 1 [oracle@node2 jack]$ sqlldr jack/jack control=jack_test_2011.ctl
 2 
 3 SQL*Loader: Release 11.2.0.1.0 - Production on Tue Dec 25 14:59:25 2012
 4 
 5 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 6 
 7 
 8 Load completed - logical record count 27381.
 9 
 ---------------验证是否加载成功-----------------------------
10 SQL> set linesize 150;
11 SQL> set pagesize 100;
12 SQL> select * from jack_test where rownum<10;
13 
14 no rows selected
15 
16 SQL> select * from jack_temp where rownum<10;
17 
18     ID NAME                             CREATED
19 ---------- ------------------------------------------------------------ ---------
20      13628 index                            08-JUN-11
21      13629 index                            08-JUN-11
22      13630 index                            08-JUN-11
23      13631 index                            08-JUN-11
24      13632 index                            08-JUN-11
25      13633 index                            08-JUN-11
26      13634 index                            08-JUN-11
27      13635 index                            08-JUN-11
28      13636 index                            08-JUN-11
29 
30 9 rows selected.
31 
32 SQL> select count(*) from jack_temp;
33 
34   COUNT(*)
35 ----------
36      27378
37 
38 SQL> select count(*) from jack_test partition (p2011);
39 
40   COUNT(*)
41 ----------
42      0
43 
44 SQL> select count(*) from jack_test;
45 
46   COUNT(*)
47 ----------
48      0

 

六、在临时表中创建全文索引和其他需要的索引

 ----------在临时表上创建全文索引，这样就可以直接把表数据和索引数据同时交换到原表中，就不需要再在原表中创建索引---------
 1 SQL> create index jack_temp_ind on jack_temp(name) indextype is ctxsys.context;
 2 
 3 Index created.
 4 
 5 SQL> select segment_name,bytes from user_segments where segment_type='TABLE' and segment_name like 'DR%' order by 1;
 6 
 7 SEGMENT_NAME                BYTES
 8 ------------------------------ ----------
 9 DR#JACK_TEST_IND0001$I            65536
10 DR#JACK_TEST_IND0001$R            65536
11 DR#JACK_TEST_IND0002$I            65536
12 DR#JACK_TEST_IND0002$R            65536
13 DR#JACK_TEST_IND0003$I            65536
14 DR#JACK_TEST_IND0003$R            65536
15 DR$JACK_TEMP_IND$I                131072      -------从全文索引基表大小上可以看出临时表上的全文索引的基表里面有数据
16 DR$JACK_TEMP_IND$R                65536
17 
18 8 rows selected.
19
  -----------查看索引和表的对应关系-------------------------------- 
20 SQL> select index_name,table_name from user_indexes where table_name in('JACK_TEMP','JACK_TEST');
21 
22 INDEX_NAME               TABLE_NAME
23 ------------------------------ ------------------------------
24 JACK_TEST_IND               JACK_TEST
25 JACK_TEMP_IND               JACK_TEMP

 

七、通过分区交换方式，将临时表中的数据和索引交换入原表

 -------------开始进行数据交换----------------------------------------
 1 SQL> alter table jack_test exchange partition p2011 with table jack_temp including indexes without validation;
 2 
 3 Table altered.
 4                                            ---上面的语句是将jack_test表的分区p2011和jack_temp临时表进行数据交换，交换的数据同时包含索引信息。
                                              ---without validation的意思是，不对表中的数据是否和分区对应进行验证，因为验证工作是需要额外开销的。
  ---------查看交换后表的信息---------------- 
 5 SQL> select count(*) from jack_temp;
 6 
 7   COUNT(*)
 8 ----------
 9      0
10 
11 SQL> select count(*) from jack_test partition(p2011);
12 
13   COUNT(*)
14 ----------
15      27378
16 
17 SQL> select segment_name,bytes from user_segments where segment_type='TABLE' and segment_name like 'DR%' order by 1;
18 
19 SEGMENT_NAME                BYTES
20 ------------------------------ ----------
21 DR#JACK_TEST_IND0001$I           131072
22 DR#JACK_TEST_IND0001$R            65536
23 DR#JACK_TEST_IND0002$I            65536
24 DR#JACK_TEST_IND0002$R            65536
25 DR#JACK_TEST_IND0003$I            65536
26 DR#JACK_TEST_IND0003$R            65536
27 DR$JACK_TEMP_IND$I            65536
28 DR$JACK_TEMP_IND$R            65536
29 
30 8 rows selected.