初識全文索引

通常來說，全文索引大多用在OLAP環(huán)境當(dāng)中，全文索引擅長于詞匯的快速搜索。 一、全文索引和普通b_tree索引對比 SQL create tablet1 (id int,name varchar(10)); Table created. SQL create indext1_ind on t1(name); Index created. SQL create tablet2 as s

通常來說，全文索引大多用在OLAP環(huán)境當(dāng)中，全文索引擅長于詞匯的快速搜索。

一、全文索引和普通b_tree索引對比

SQL> create tablet1 (id int,name varchar(10));

Table created.

SQL> create indext1_ind on t1(name);

Index created.

SQL> create tablet2 as select * from t1;

Table created.

SQL> create indext2_ind on t2(name) indextype is ctxsys.context;

Index created.

SQL> select *from t1 where name like '%tom%';

ID NAME

--------------------

1 tom

2 tom tom

2 tom tom

Execution Plan

----------------------------------------------------------

Plan hash value:3589342044

--------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 10 | 2 (0)| 00:00:01 |

|* 2 | INDEX FULL SCAN | T1_IND| 1 | | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------------

PredicateInformation (identified by operation id):

---------------------------------------------------

2 - filter("NAME" LIKE '%tom%' AND"NAME" IS NOT NULL)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

676 bytes sent via SQL*Net to client

519 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3 rows processed

SQL> select *from t2 where contains(name,'tom')>0;

ID NAME

--------------------

1 tom

2 tom tom

2 tom tom

Execution Plan

----------------------------------------------------------

Plan hash value:785228215

--------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 | 30 | 7 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 3 | 30 | 7 (0)| 00:00:01 |

|* 2 | DOMAIN INDEX | T2_IND| | | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------------

PredicateInformation (identified by operation id):

---------------------------------------------------

2 -access("CTXSYS"."CONTAINS"("NAME",'tom')>0)

Statistics

----------------------------------------------------------

10 recursive calls

0 db block gets

14 consistent gets

0 physical reads

0 redo size

676 bytes sent via SQL*Net to client

519 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

3 rows processed

SQL> selectobject_name,object_type from user_objects order by 1;

OBJECT_NAME OBJECT_TYPE

-------------------------------------------------

--DR開頭的四張表為全文索引的基表

DR$T2_IND$X INDEX

DRC$T2_IND$R INDEX

SYS_IL0000236119C00006$$ INDEX

SYS_IL0000236124C00002$$ INDEX

SYS_IOT_TOP_236122 INDEX

SYS_IOT_TOP_236128 INDEX

SYS_LOB0000236119C00006$$ LOB

OBJECT_NAME OBJECT_TYPE

-------------------------------------------------

SYS_LOB0000236124C00002$$ LOB

T1 TABLE

T1_IND INDEX

T2 TABLE

T2_IND INDEX

二、DML操作對全文索引的影響

以context方式創(chuàng)建的全文索引并不是基于事務(wù)的，默認(rèn)情況下，即使一個dml操作提交，信息也不會更新到全文索引中。

1、insert 操作

SQL> create tablet(name varchar2(30));

Table created.

SQL> create indext_ind on t(name) indextype is ctxsys.context;

Index created.

SQL> insert intot values('i am an oracle dba');

1 row created.

SQL> commit；

insert數(shù)據(jù)已提交，我們看看全文索引是否已更新

SQL> setautotrace on

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

NAME

------------------------------

i am an oracle dba

Execution Plan

----------------------------------------------------------

Plan hash value:1601196873

--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 17 | 2 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T | 1 | 17 | 2 (0)| 00:00:01 |

--------------------------------------------------------------------------

PredicateInformation (identified by operation id):

---------------------------------------------------

1 - filter("NAME" IS NOT NULL AND"NAME" LIKE '%dba%')

Note

-----

- dynamic sampling used for this statement(level=2)

Statistics

----------------------------------------------------------

5 recursive calls

0 db block gets

15 consistent gets

0 physical reads

0 redo size

538 bytes sent via SQL*Net to client

520 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> set line 200

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

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value:315187259

-------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 29 | 4 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 29 | 4 (0)| 00:00:01 |

|* 2 | DOMAIN INDEX | T_IND| | | 4 (0)| 00:00:01 |

-------------------------------------------------------------------------------------

PredicateInformation (identified by operation id):

---------------------------------------------------

2 -access("CTXSYS"."CONTAINS"("NAME",'dba')>0)

Note

-----

- dynamic sampling used for this statement(level=2)

Statistics

----------------------------------------------------------

1829 recursive calls

0 db block gets

2696 consistent gets

30 physical reads

0 redo size

332 bytes sent via SQL*Net to client

509 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

164 sorts (memory)

0 sorts (disk)

0 rows processed

以上發(fā)現(xiàn)，全文索引并沒有自動更新，而是把記錄存放在線面的ctxsys.dr$pending表中。

SQL> setautotrace off

SQL> select *from ctxsys.dr$pending;

PND_CID PND_PID PND_ROWID PND_TIMESTAMP P

-------------------- ------------------ ------------------ -

1084 0 AABGmVAAEAAAADmAAA 03-APR-14 N

SQL> insert intot values('he is an oracle dba');

1 row created.

SQL> commit;

Commit complete.

SQL> select *from ctxsys.dr$pending;

PND_CID PND_PID PND_ROWID PND_TIMESTAMP P

-------------------- ------------------ ------------------ -

1084 0 AABGmVAAEAAAADmAAA 03-APR-14 N

1084 0 AABGmVAAEAAAADmAAB 03-APR-14 N

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

no rows selected

為了把信息同步到全文索引中，我們需要手工同步：

SQL> alter indext_ind rebuild parameters ('sync');

Index altered.

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

NAME

------------------------------

i am an oracle dba

he is an oracle dba

SQL> select *from ctxsys.dr$pending;

no rows selected

2、delete操作

SQL> select *from t;

NAME

------------------------------

i am an oracle dba

he is an oracle dba

SQL> delete fromt where name='he is an oracle dba';

1 row deleted.

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

NAME

------------------------------

i am an oracle dba

SQL> select *from ctxsys.dr$pending;

no rows selected

SQL> select *from ctxsys.dr$delete;

DEL_IDX_IDDEL_IXP_ID DEL_DOCID

-------------------- ----------

1084 0 2

這里我們看到全文索引立即生效了，至于ctxsys.dr$delete里面的數(shù)據(jù)是delete操作的中間狀態(tài)，用來維護(hù)一個事物，無論事物提交或者回滾。

SQL> rollback;

Rollback complete.

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

NAME

------------------------------

i am an oracle dba

he is an oracle dba

SQL> select *from ctxsys.dr$delete;

no rows selected

3、update操作

update操作相當(dāng)于delete+insert操作，所以默認(rèn)情況下需要手動刷新全文索引。

SQL> update t setname='oracle dba' where name='i am an oracle dba';

1 row updated.

SQL> select *from ctxsys.dr$delete;

DEL_IDX_IDDEL_IXP_ID DEL_DOCID

-------------------- ----------

1084 0 1

SQL> select *from ctxsys.dr$pending;

PND_CID PND_PID PND_ROWID PND_TIMESTAMP P

-------------------- ------------------ ------------------ -

1084 0 AABGmVAAEAAAADmAAA 03-APR-14 N

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

NAME

------------------------------

he is an oracle dba

SQL> alter indext_ind rebuild parameters ('sync');

Index altered.

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

NAME

------------------------------

he is an oracle dba

oracle dba

由于全文索引創(chuàng)建對象大多是海量數(shù)據(jù)的表，dml操作如果實(shí)時更新會影響到系統(tǒng)性能。創(chuàng)建全文索引同步的三個選項(xiàng)：

manual：默認(rèn)選項(xiàng)

every：在一個時間段后更新索引

on commitdml：在事務(wù)提交后更新索引

語法如下：

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

查看全文索引信息和性能的工具包c(diǎn)tx_report

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com