全文索引

全文索引的作用包括：1.提高查询速度，2.切词，3.针对所有数据类型（包括blob等）进行全文检索，4.可以对超过150种文件类型（如doc，txt，pdf，xml）进行检索

1、在sysdba权限下验证是否有ctxsys用户和ctxapp角色：
sql>select username from all_users;

2、如果没有该用户，则需要打开dbca工具中选择configrue database options，然后选择所有数据库组件安装即可。

SQL> create table t(name varchar2(30));

Table created.

3、在某个字段上创建全文索引
SQL> create index t_idx on t(name) indextype is ctxsys.context;

Index created.

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

1 row created.

SQL> commit;

Commit complete.
4、开启 SQL跟踪
SQL> set autotrace on exp;
SQL> select * from t where name like '%DBA';

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

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("NAME" LIKE '%DBA')

Note
-----
   - dynamic sampling used for this statement
 此时走的是全表扫描，而非索引，目前索引没有被使用。
  
5、提高查询速度

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

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 4021287357

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

| 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_IDX |       |       |     4   (0)| 00:00
:01 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

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

Note
-----
   - dynamic sampling used for this statement

SQL> set autotrace off;

SQL> select * from ctxsys.DR$PENDING;

   PND_CID    PND_PID PND_ROWID          PND_TIMES P
---------- ---------- ------------------ --------- -
      1060          0 AAAM7cAABAAAO1CAAA 17-JUL-13 N

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

1 row created.

SQL> commit;

Commit complete.

需要手动修改，才可以把索引存放全文索引中
SQL> alter index t_idx rebuild parameters('sync');

Index altered.

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

NAME
------------------------------
1 am an Oracle DBA
Oracle DBA

SQL> select * from ctxsys.DR$PENDING;

no rows selected

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24492954/viewspace-766465/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24492954/viewspace-766465/