Oracle全文索引的性能优势实例

一、实验说明：

     操作系统：rhel 5.4 x86

    数据库：Oracle 11g R2

二、操作步骤：

  2.1、首先创建一个表t_btree，并创建B-Tree索引，索引键是object_name:

SQL> create table t_btree as select * from dba_objects;

Table created.

SQL> create index ind_btree on t_btree(object_name);

Index created.

接着是执行下面的查询语句两次：

SQL> set linesize 150;
SQL> set autotrace on;
SQL> select count(*) from t_btree where t_btree.object_name like ‘%ObjectStreamClass%‘;

  COUNT(*)
----------
    84

Execution Plan
----------------------------------------------------------
Plan hash value: 3266099700

-----------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time      |
-----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT      |      |    1 |    66 |  103  (0)| 00:00:02 |
|  1 |  SORT AGGREGATE      |      |    1 |    66 |          |      |
|*  2 |  INDEX FAST FULL SCAN| IND_BTREE |    12 |  792 |  103  (0)| 00:00:02 |
-----------------------------------------------------------------------------------

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

  2 - filter("T_BTREE"."OBJECT_NAME" IS NOT NULL AND
          "T_BTREE"."OBJECT_NAME" LIKE ‘%ObjectStreamClass%‘)

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

Statistics
----------------------------------------------------------
    28  recursive calls
      0  db block gets
    454  consistent gets
    726  physical reads
      0  redo size
    422  bytes sent via SQL*Net to client
    419  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

SQL> select count(*) from t_btree where t_btree.object_name like ‘%ObjectStreamClass%‘;

  COUNT(*)
----------
    84

Execution Plan
----------------------------------------------------------
Plan hash value: 3266099700

-----------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time      |
-----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT      |      |    1 |    66 |  103  (0)| 00:00:02 |
|  1 |  SORT AGGREGATE      |      |    1 |    66 |          |      |
|*  2 |  INDEX FAST FULL SCAN| IND_BTREE |    12 |  792 |  103  (0)| 00:00:02 |
-----------------------------------------------------------------------------------

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

  2 - filter("T_BTREE"."OBJECT_NAME" IS NOT NULL AND
          "T_BTREE"."OBJECT_NAME" LIKE ‘%ObjectStreamClass%‘)

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

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
    369  consistent gets
      0  physical reads
      0  redo size
    422  bytes sent via SQL*Net to client
    419  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

2.2、创建表t_bmap，并创建BitMap索引：

1 SQL> create table t_bmap as select * from dba_objects;
2 
3 Table created.
4 SQL> create bitmap index ind_bmap on t_bmap(object_name);
5 
6 Index created。

执行之前的同样的语句查询：

SQL> select count(*) from t_bmap where t_bmap.object_name like ‘%ObjectStreamClass%‘;

  COUNT(*)
----------
    84

Execution Plan
----------------------------------------------------------
Plan hash value: 891302759

------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |      |    1 |    66 |  263  (0)| 00:00:04 |
|  1 |  SORT AGGREGATE          |      |    1 |    66 |          |      |
|  2 |  BITMAP CONVERSION COUNT    |      |    12 |  792 |  263  (0)| 00:00:04 |
|*  3 |    BITMAP INDEX FAST FULL SCAN| IND_BMAP |    |    |          |      |
------------------------------------------------------------------------------------------

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

  3 - filter("T_BMAP"."OBJECT_NAME" IS NOT NULL AND "T_BMAP"."OBJECT_NAME" LIKE
          ‘%ObjectStreamClass%‘)

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

Statistics
----------------------------------------------------------
    28  recursive calls
      0  db block gets
    360  consistent gets
    591  physical reads
      0  redo size
    422  bytes sent via SQL*Net to client
    419  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

SQL> select count(*) from t_bmap where t_bmap.object_name like ‘%ObjectStreamClass%‘;

  COUNT(*)
----------
    84

Execution Plan
----------------------------------------------------------
Plan hash value: 891302759

------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |      |    1 |    66 |  263  (0)| 00:00:04 |
|  1 |  SORT AGGREGATE          |      |    1 |    66 |          |      |
|  2 |  BITMAP CONVERSION COUNT    |      |    12 |  792 |  263  (0)| 00:00:04 |
|*  3 |    BITMAP INDEX FAST FULL SCAN| IND_BMAP |    |    |          |      |
------------------------------------------------------------------------------------------

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

  3 - filter("T_BMAP"."OBJECT_NAME" IS NOT NULL AND "T_BMAP"."OBJECT_NAME" LIKE
          ‘%ObjectStreamClass%‘)

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

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
    285  consistent gets
      0  physical reads
      0  redo size
    422  bytes sent via SQL*Net to client
    419  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

2.3、创建表t_all，并创建全文索引：

SQL> create table t_all as select * from dba_objects;

Table created.

SQL> create index ind_all on t_all(object_name) indextype is ctxsys.context;

Index created.

下面改变一下查询语句，但是需要输出的结果是跟之前的一样：

SQL> select count(*) from t_all where contains(t_all.object_name,‘%ObjectStreamClass%‘)>0;

  COUNT(*)
----------
    84

Execution Plan
----------------------------------------------------------
Plan hash value: 3532980284

----------------------------------------------------------------------------
| Id  | Operation    | Name    | Rows  | Bytes | Cost (%CPU)| Time      |
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT |      |    1 |    78 |    4  (0)| 00:00:01 |
|  1 |  SORT AGGREGATE  |      |    1 |    78 |        |      |
|*  2 |  DOMAIN INDEX    | IND_ALL |    35 |  2730 |    4  (0)| 00:00:01 |
----------------------------------------------------------------------------

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

  2 - access("CTXSYS"."CONTAINS"("T_ALL"."OBJECT_NAME",‘%ObjectStreamCl
          ass%‘)>0)

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

Statistics
----------------------------------------------------------
      2221  recursive calls
      0  db block gets
      2228  consistent gets
    267  physical reads
      0  redo size
    422  bytes sent via SQL*Net to client
    419  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
    254  sorts (memory)
      0  sorts (disk)
      1  rows processed

SQL> select count(*) from t_all where contains(t_all.object_name,‘%ObjectStreamClass%‘)>0;

  COUNT(*)
----------
    84

Execution Plan
----------------------------------------------------------
Plan hash value: 3532980284

----------------------------------------------------------------------------
| Id  | Operation    | Name    | Rows  | Bytes | Cost (%CPU)| Time      |
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT |      |    1 |    78 |    4  (0)| 00:00:01 |
|  1 |  SORT AGGREGATE  |      |    1 |    78 |        |      |
|*  2 |  DOMAIN INDEX    | IND_ALL |    35 |  2730 |    4  (0)| 00:00:01 |
----------------------------------------------------------------------------

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

  2 - access("CTXSYS"."CONTAINS"("T_ALL"."OBJECT_NAME",‘%ObjectStreamCl
          ass%‘)>0)

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

Statistics
----------------------------------------------------------
    48  recursive calls
      0  db block gets
    348  consistent gets
      0  physical reads
      0  redo size
    422  bytes sent via SQL*Net to client
    419  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed