Full-text index

Generally, full-text indexes are mostly used in OLAP environments. Full-text indexes are good at quick word search. 1. Comparison between full-text indexes and ordinary B _tree indexes SQLcreatetablet1 (idint, namevarchar (10); Tablecreated. SQLcreateindext1_indont1 (name); Indexcreated. SQLcreatetablet2ass

Generally, full-text indexes are mostly used in OLAP environments. Full-text indexes are good at quick word search. I. Comparison between full-text indexes and ordinary B _tree indexes SQL create tablet1 (id int, name varchar (10); Table created. SQL create indext1_ind on t1 (name); Index created. SQL create tablet2 as s

Generally, full-text indexes are mostly used in OLAP environments. Full-text indexes are good at quick word search.

I. Comparison between full-text indexes and ordinary B _tree Indexes

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

2 tom

Execution Plan

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

Plan hash value: 3589342044

Bytes --------------------------------------------------------------------------------------

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

Bytes --------------------------------------------------------------------------------------

| 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 |

Bytes --------------------------------------------------------------------------------------

PredicateInformation (identified by operation id ):

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

2-filter ("NAME" LIKE '% tom %' AND "NAME" is not null)

Statistics

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

0 recursive cballs

0 db block gets

4 consistent gets

0 physical reads

0 redo size

676 bytes sent via SQL * Net to client

519 bytes encoded ed 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

2 tom

Execution Plan

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

Plan hash value: 785228215

Bytes --------------------------------------------------------------------------------------

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

Bytes --------------------------------------------------------------------------------------

| 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 |

Bytes --------------------------------------------------------------------------------------

PredicateInformation (identified by operation id ):

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

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

Statistics

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

10 recursive CILS

0 db block gets

14 consistent gets

0 physical reads

0 redo size

676 bytes sent via SQL * Net to client

519 bytes encoded ed 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

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

-- Four tables starting with DR are base tables with full-text indexes

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

Ii. Impact of DML operations on full-text indexing

The full-text index created in context mode is not based on transactions. By default, even if a dml operation is committed, the information will not be updated to the full-text index.

1. insert operation

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

Table created.

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

Index created.

SQL> insert into T values ('I am an oracle dba ');

1 row created.

SQL> commit;

The insert data has been submitted. Let's see if the full-text index has been updated.

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 cballs

0 db block gets

15 consistent gets

0 physical reads

0 redo size

538 bytes sent via SQL * Net to client

520 bytes encoded ed via SQL * Net from client

2 SQL * Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL & gt; set line 200

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

No rows selected

Execution Plan

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

Plan hash value: 315187259

Bytes -------------------------------------------------------------------------------------

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

Bytes -------------------------------------------------------------------------------------

| 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 |

Bytes -------------------------------------------------------------------------------------

PredicateInformation (identified by operation id ):

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

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

Note

-----

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

Statistics

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

1829 recursive cballs

0 db block gets

2696 consistent gets

30 physical reads

0 redo size

332 bytes sent via SQL * Net to client

509 bytes encoded ed via SQL * Net from client

1 SQL * Net roundtrips to/from client

164 sorts (memory)

0 sorts (disk)

0 rows processed

The above results show that full-text indexes are not automatically updated. Instead, records are stored in the ctxsys. dr $ pending table online.

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 into T 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

To synchronize the information to the full-text index, we need to manually synchronize:

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 operation

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

The full-text index takes effect immediately. The data in ctxsys. dr $ delete is the intermediate state of the delete operation, which is used to maintain a transaction, whether committed or rolled back.

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 operation

The update operation is equivalent to the delete + insert operation. By default, you must manually refresh the full-text index.

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

Because most of the objects created by full-text indexing are tables with massive data, real-time updates to dml operations will affect system performance. Three options for creating full-text index synchronization:

Manual: Default Option

Every: update the index after a time period

On commitdml: update the index after the transaction is committed.

Syntax:

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

Ctx_report, a toolkit for viewing full-text index information and performance