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

最新推荐文章于 2024-07-22 15:12:37 发布
最新推荐文章于 2024-07-22 15:12:37 发布
阅读量137 收藏
点赞数
文章标签： 数据库

全文索引表的分区交换案例第一步 创建分区表
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/

cuichangzhi5476
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle交换索引,oracle交换分区所引起的索引失效问题探究测试
weixin_39695374的博客
04-04 769
oracle交换分区(exchangepartition)提供了一种方式，让你在表与表或分区与分区之间迁移数据，由于其采用了更改数据字典的方式，几乎不涉及io操作，因此效率极高。exchangepartition适用于所有分区格式，可以将数据从分区表迁移到非分区表，也可以从非分区表迁移至分区表，但不支持range partition和range partition之间交换。探究测试一：创建本地索引...
oracle模糊查询:分区局部全文索引方式（四）
lpxuan151009的专栏
06-30 1594
DATABASE:dwtest(210开发库)--按根级行业ID和大洲进行混合分区DROP TABLE iquery.lpx_kw_tmp02_bak2;CREATE TABLE iquery.lpx_kw_tmp02_bak2( KEYWORDS                                           VARCHAR2(256) ,REGIN_
oracle分区交换
siyanyanyanyai的专栏
04-15 1713
在Oracle数据库开发中，对于这种大数据的转移可以使用分区表交换技术实现。即使你一次转移的数据量几亿甚至几十亿也没有关系，转移时间依然是毫秒级的。这个方法大体流程是这样：首先，你需要将当前表修改为分区表，找到分区字段很关键；其次，这个分区表的索引都建立成本地索引，全局索引就不要了，原因后面介绍；再次，建立一个对应的临时非分区表，表结构和这个一样；最后使用alter table table_n
分区交换及分区索引重建
hym_1989的博客
05-08 1439
create index ind_l_object_id on test(object_id) local ;--分区交换：PART_OWNER9 数据与  bk_test 表互换 ；UPDATE GLOBAL INDEXES ：保证原来的索引还有效alter table test exchange partition PART_OWNER9  with table bk_test UPDATE ...
Oracle分区键与分区本地索引
我的Oracle DBA之路
07-15 578
Oracle分区键与分区本地索引http://bbs.chinaunix.net/thread-1851512-1-1.html
Oracle查询优化改写 技巧与案例
05-24
8. **分析统计信息**：定期执行ANALYZE命令更新表和索引的统计信息，帮助Oracle的查询优化器做出更准确的执行计划。 9. **使用EXPLAIN PLAN**：EXPLAIN PLAN是调试和理解查询执行计划的重要工具，通过它可以看到...
oracle 数据库表生成器
08-19
在大量数据导入后，可能需要进行索引创建、分区策略调整、统计信息收集等操作，以提升查询效率。同时，为了确保数据安全，还需要设置合适的权限和角色，限制对敏感数据的访问。 总的来说，"Oracle数据库表生成器...
中型三甲医院Oracle数据库坏块恢复方法探讨.pdf
10-09
首先，通过收集错误信息，如查看警告日志、使用SQL查询确定坏块所在的对象（表空间、段类型、所有者、段名称、分区名称），然后使用DBV工具或V$DATABASE_BLOCK_CORRUPTION视图检查其他潜在坏块。 在案例中，故障...
oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 连接字符串
05-06
ORACLE数据库系统是美国ORACLE公司（甲骨文）提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。  拉里•埃里森  就业前景 从就业与择业的...
Oracle DBA 参考手册
09-26
8.4.13. 交换表分区 36 8.5. 索引 37 8.6. 约束 37 8.7. LOAD数据 38 8.8. reorganizing data 38 8.9. 其他技巧 39 9. 数据库安全管理 40 9.1. 定义 40 9.2. 安全内容及工作 40 9.2.1. 安全内容 40 9.2.2. 安全工作 ...
oracle全文检索 分区表,oracle全文检索笔记
weixin_33565515的博客
04-13 158
1、删除词法解析器exec ctx_ddl.drop_preference('my_lexer');2、创建中文词法解析器exec ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer');3、创建全文索引，多字段EXEC ctx_ddl.create_preference('ctx_idx_jdw_person_pref','MU...
Oracle分区表全局索引添加分区时不会失效
congjianxie6566的博客
01-30 636
COALESCE PARTITION | SUBPARTITION (hash) DROP PARTITION | SUBPARTITION EXCHANGE PARTITION | SUBPARTITION MERGE ...
oracle交换分区索引失效,解决ORA-14098分区交换索引不匹配错误
weixin_42099942的博客
04-12 1058
上周在客户一套BRM系统上执行分区交换Exchange Partition操作的时候出现了ORA-14098错误，该错误是由于分区表上的LOCAL分区索引与非分区表上的索引不匹配造成的，我们来看一下这个错误:[oracle@rh2 ~]$ oerr ora 1409814098, 00000, "index mismatch for tables in ALTER TABLE EXCHANGE P...
Oracle 交换分区
热门推荐
极致，细节
04-24 1万+
交换分区 利用Oracle 交换分区你可以实现： ♦将一个分区(或子分区)转换成一个普通表。 ♦将一个普通表转换成一个分区表的分区(或子分区)。 ♦将一个哈希分区表转换成复合*哈希分区表的分区。 ♦将一个复合*哈希分区表的分区转换成一个哈希分区表。 ♦将一个(范围|列表)分区表转换成一个分区组合* 列表范围分区表。 ♦将一个分区的复合*——(范围|列表)分区表转换到一个范围|列表
oracle交换索引,原来分区交换会自动使索引不可用
weixin_42150341的博客
04-04 190
如果和索引一起交换，则会保持交换前的可用性SQL> alter table tp MODIFY PARTITION2p11 REBUILD UNUSABLE LOCAL INDEXES;Table altered.Elapsed: 00:00:00.01SQL> 22* p11 REBUILD UNUSABLE LOCAL INDEXESSQL> c/11/122* p12 ...
Oracle之表分区、分区索引（一）
Leon_Jinhai_Sun的博客
05-14 2070
继续往下讲就是分区了 对于ORACLE来讲,分区是咱们开发中最常用的,什么样的场景下要进行分区,主要是针对于大数据量的表,频繁查询的表, 我只是说数据量大的表,并没有说物理的这个大,比如你搞一个文件表,数据库文件,里面来一个file文件,你搞一个file文件这种表, 然后你去做分区,那样的话其实也不是很快的,我们这里说的怎么去做分区,是按照数据量来的,不是按照物理的大小来的,按照物理...
OAuth2.0 or Spring Session or 单点登录流程
qq_53374893的博客
07-20 333
一句话精辟解释： 在过滤器放行的时候，偷偷的把原生的 request 和 response 对象换成了它的实现，也就是 调用getSession 的时候拿到的 其实是对redis 操作的Session。但其他的操作还是使用原生的，只不过重写的方法，调用的是子类的，也就是往 redis 里放入 Session，把原生的操作给替换了！分布式Session原理，使用子域名，的时候放入父域名的 JsessionId 然后将这个ID 的所存的内容放入Redis ，这样实现不同域名共享同一sessionID.
基于JSP的高校信息资源共享平台
最新发布
heye0910032的博客
07-22 474
高校信息资源共享平台的开发和实现，为高校信息资源的管理和共享提供了一个有效的解决方案。通过使用JSP技术和MySQL数据库，本系统实现了一个稳定、安全、易用的高校信息资源管理平台。虽然在开发过程中遇到了一些技术挑战，但通过不断学习和实践，最终完成了一个能够满足用户需求的系统。未来，我们将继续优化系统功能，提升用户体验，以期达到更高的应用价值和社会效益。高校信息资源共享平台的开发和实现，为高校信息资源的管理和共享提供了一个有效的解决方案。
Oracle分区表与索引详解：创建、管理与实战应用
Oracle分区表与分区索引是数据库管理系统中的一种高级特性，它通过将数据分割存储在不同的物理结构中，提高查询性能和管理复杂度。本文档深入探讨了如何在Oracle环境中创建、管理和维护这两种关键元素。 首先，分区...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值