读书笔记整理十二：全文索引

《让oracle跑的更快2》读书笔记整理

读书笔记整理十二：全文索引
Oracle的全文索引主要属于数据仓库或者OLAP系统范畴，在OLTP系统中用到全文索引的时候并不多
全文索引属于语言文字范畴，它的属性和语言文字的属性直接关联起来，通过将文字按照某种语言
进行词汇拆分，重新将数据组合存储，来达到快速检索的目的。

即使一个字段中有多个重复的词汇，Oracle也会区分它们是否属于同一条记录，并计算出正确的结果
SQL> create table t1(id int,name varchar(10));

Table created.

SQL> create index t1_text_idx on t1(name) indextype is ctxsys.context;

Index created.
SQL> select table_name,index_name from user_indexes;

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
DR$T1_TEXT_IDX$I               SYS_IL0000053109C00006$$
DR$T1_TEXT_IDX$I               DR$T1_TEXT_IDX$X
T1                             T1_TEXT_IDX
DR$T1_TEXT_IDX$K               SYS_IOT_TOP_53112
DR$T1_TEXT_IDX$R               SYS_IL0000053114C00002$$
DR$T1_TEXT_IDX$N               SYS_IOT_TOP_53117

可以看到Oracle创建了以DR$开头的表，这是全文索引的基表，所有全文索引的数据都保留在这些基表里面，
而那些索引都分别创建在各个基表上，用于Oracle快速访问数据。而T1_TEXT_IDX只是个索引的名称，它本身
并不占有空间，如下面所示
SQL> select segment_name,segment_type,bytes from user_segments order by 2;
SEGMENT_NAME              SEGMENT_TYPE            BYTES
------------------------- ------------------ ----------
SYS_IOT_TOP_53112         INDEX                   65536
SYS_IOT_TOP_53117         INDEX                   65536
DR$T1_TEXT_IDX$X          INDEX                   65536
SYS_IL0000053109C00006$$  LOBINDEX                65536
SYS_IL0000053114C00002$$  LOBINDEX                65536
SYS_LOB0000053109C00006$$ LOBSEGMENT              65536
SYS_LOB0000053114C00002$$ LOBSEGMENT              65536
DR$T1_TEXT_IDX$I          TABLE                   65536
DR$T1_TEXT_IDX$R          TABLE                   65536

查询段对象里面没有刚才建的全文索引T1_TEXT_IDX，而实际占空间的都是以DR$开头的对象，也就是说全文索引本身
并不包含数据。
SQL> select object_name from user_objects where object_name like 'DR$%';

OBJECT_NAME
--------------------------------------------------------------------------------
DR$T1_TEXT_IDX$I          ------->>>(I--Index data table)
DR$T1_TEXT_IDX$K          ------->>>(K--keymap)
DR$T1_TEXT_IDX$N          ------->>>(N--Negative list table)
DR$T1_TEXT_IDX$R          ------->>>(R--rowid table)

全文索引占得空间会比较大，在设计数据库时一定要注意。

分区数据交换
alter table t1 exchange partition p2009 with table t1_temp including indexes without validation;
上述语句就是将T1表的分区p2009和t1_temp临时表进行数据交换，交换的数据同时包含索引信息

交换分区要注意同步索引的信息:
alter index t1_temp_idx rebuild parameters('sync');

在海量的数据库设计中，使用全文索引需要注意以下2点:
全文索引所占的空间
全文索引和DML操作

以context方式创建的全文索引不是基于事务的(ctxcat是基于事务的全文索引)，索引在默认情况下，当发出一个DML
操作时，即使提交，这些修改的信息也不会更新到索引中，要多加注意！

insert的信息提交之后,oracle会把更新的记录存放到DR$PENDING表中，告诉全文索引，有一条数据需要处理.
select * from ctxsys.DR$PENDING;
全文索引需要手动同步才能更新统计信息
alter index t_idx rebuild parameters('sync);
更新之后再查结果就会有了
select * from t where contains(name,'DBA')>0;
没有更新前，上面语句是查不到任何记录的

删除记录时，记录存储在DR$DELETE表中，而且是立即生效的。在全文索引中,delete操作能维护事务，能自动生效，
能自动更新全文索引的信息。

UPDATE操作

SQL> select * from t;

NAME
------------------------------
I am an Oracle DBA
Oracle DBA

SQL> update t set name='DBA' where name='Oracle DBA';

1 row updated.

SQL> select * from ctxsys.DR$DELETE;

DEL_IDX_ID DEL_IXP_ID  DEL_DOCID
---------- ---------- ----------
      1081          0          3

SQL> SELECT * FROM CTXSYS.DR$PENDING;

   PND_CID    PND_PID PND_ROWID          PND_TIMES P
---------- ---------- ------------------ --------- -
      1081          0 AAAM+AAAHAAAAGMAAC 11-AUG-12 N
我们更新一条记录去查询，发现DR$DELETE和DR$PENDING都有一条记录。说明update操作对于全文索引来说
是一个delete操作再接着一个insert操作
SQL> COMMIT;

Commit complete.

SQL> select * from ctxsys.DR$delete;

no rows selected

SQL> select * from ctxsys.DR$PENDING;

   PND_CID    PND_PID PND_ROWID          PND_TIMES P
---------- ---------- ------------------ --------- -
      1081          0 AAAM+AAAHAAAAGMAAC 11-AUG-12 N

SQL> SELECT * from t where contains(name,'DBA')>0;

NAME
------------------------------
I am an Oracle DBA

由此可以看出delete操作确实更新了索引信息，但是insert的操作没有更新，所以查到的只是delete之后的。

SQL> select * from t where name like '%DBA%';

NAME
------------------------------
I am an Oracle DBA
DBA
由此可以看出表的数据已经完全更新了，只是索引没有完全更新
SQL> alter index t_idx rebuild parameters('sync');

Index altered.

SQL> SELECT * from t where contains(name,'DBA')>0;

NAME
------------------------------
I am an Oracle DBA
DBA
手工更新索引信息后，数据才被更新。

有一种方法可以在进行DML操作时，实时更新全文索引的相关信息，要注意此时对数据库性能的影响，一般
默认是关闭的。SQL语法如下:
SQL> drop index t_idx;

Index dropped.

SQL> create index t_idx on t(name) indextype is ctxsys.context parameters('sync (on commit)');

Index created.

SQL> select * from t;

NAME
------------------------------
I am an Oracle DBA
DBA

SQL> insert into t values('Oracle DBA');

1 row created.

SQL> select * from ctxsys.DR$PENDING;

   PND_CID    PND_PID PND_ROWID          PND_TIMES P
---------- ---------- ------------------ --------- -
      1082          0 AAAM+AAAHAAAAGMAAB 11-AUG-12 N
SQL> select * from t where contains(name,'DBA')>0;

NAME
------------------------------
I am an Oracle DBA
DBA
没有提交前，可以看到此时还查不到更新的信息。
SQL> commit;

Commit complete.

SQL> select * from ctxsys.DR$PENDING;

no rows selected

SQL> select * from t where contains(name,'DBA')>0;

NAME
------------------------------
I am an Oracle DBA
DBA
Oracle DBA
提交后，索引就更新了。

创建全文索引并且实时更新索引信息的语句如下:
create index t_idx on t(name) indextype is ctxsys.context parameters('sync (on commit)');
sync有三个选项
manual:默认选项
EVERY "interval-string":允许一个时间段，在一个时间段后更新索引信息
ON COMMIT DML:操作提交时生效

查询全文索引的工具包CTX_REPORT

PS:去年买了这本书，一直都没有看，最近偶尔翻一下，确实能学到不少东西。可以看出作者对海量数据仓库的管理确实有丰富的经验，我辈还得需要努力啊