用示例说明全文索引的性能优势

一、实验说明：

     操作系统：rhel 5.4 x86

     数据库：Oracle 11g R2

二、操作步骤：

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

1 SQL> create table t_btree as select * from dba_objects;
2 
3 Table created.
4 
5 SQL> create index ind_btree on t_btree(object_name);
6 
7 Index created.

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

 1 SQL> set linesize 150;
 2 SQL> set autotrace on;
 3 SQL> select count(*) from t_btree where t_btree.object_name like '%ObjectStreamClass%';
 4 
 5   COUNT(*)
 6 ----------
 7     84
 8 
 9 
10 Execution Plan
11 ----------------------------------------------------------
12 Plan hash value: 3266099700
13 
14 -----------------------------------------------------------------------------------
15 | Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time      |
16 -----------------------------------------------------------------------------------
17 |   0 | SELECT STATEMENT      |       |    1 |    66 |   103   (0)| 00:00:02 |
18 |   1 |  SORT AGGREGATE       |       |    1 |    66 |           |      |
19 |*  2 |   INDEX FAST FULL SCAN| IND_BTREE |    12 |   792 |   103   (0)| 00:00:02 |
20 -----------------------------------------------------------------------------------
21 
22 Predicate Information (identified by operation id):
23 ---------------------------------------------------
24 
25    2 - filter("T_BTREE"."OBJECT_NAME" IS NOT NULL AND
26           "T_BTREE"."OBJECT_NAME" LIKE '%ObjectStreamClass%')
27 
28 Note
29 -----
30    - dynamic sampling used for this statement (level=2)
31 
32 
33 Statistics
34 ----------------------------------------------------------
35      28  recursive calls
36       0  db block gets
37     454  consistent gets
38     726  physical reads
39       0  redo size
40     422  bytes sent via SQL*Net to client
41     419  bytes received via SQL*Net from client
42       2  SQL*Net roundtrips to/from client
43       0  sorts (memory)
44       0  sorts (disk)
45       1  rows processed
46 
47 SQL> select count(*) from t_btree where t_btree.object_name like '%ObjectStreamClass%';
48 
49   COUNT(*)
50 ----------
51     84
52 
53 
54 Execution Plan
55 ----------------------------------------------------------
56 Plan hash value: 3266099700
57 
58 -----------------------------------------------------------------------------------
59 | Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time      |
60 -----------------------------------------------------------------------------------
61 |   0 | SELECT STATEMENT      |       |    1 |    66 |   103   (0)| 00:00:02 |
62 |   1 |  SORT AGGREGATE       |       |    1 |    66 |           |      |
63 |*  2 |   INDEX FAST FULL SCAN| IND_BTREE |    12 |   792 |   103   (0)| 00:00:02 |
64 -----------------------------------------------------------------------------------
65 
66 Predicate Information (identified by operation id):
67 ---------------------------------------------------
68 
69    2 - filter("T_BTREE"."OBJECT_NAME" IS NOT NULL AND
70           "T_BTREE"."OBJECT_NAME" LIKE '%ObjectStreamClass%')
71 
72 Note
73 -----
74    - dynamic sampling used for this statement (level=2)
75 
76 
77 Statistics
78 ----------------------------------------------------------
79       0  recursive calls
80       0  db block gets
81     369  consistent gets
82       0  physical reads
83       0  redo size
84     422  bytes sent via SQL*Net to client
85     419  bytes received via SQL*Net from client
86       2  SQL*Net roundtrips to/from client
87       0  sorts (memory)
88       0  sorts (disk)
89       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。

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

 1 SQL> select count(*) from t_bmap where t_bmap.object_name like '%ObjectStreamClass%';
 2 
 3   COUNT(*)
 4 ----------
 5     84
 6 
 7 
 8 Execution Plan
 9 ----------------------------------------------------------
10 Plan hash value: 891302759
11 
12 ------------------------------------------------------------------------------------------
13 | Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
14 ------------------------------------------------------------------------------------------
15 |   0 | SELECT STATEMENT          |      |     1 |    66 |   263   (0)| 00:00:04 |
16 |   1 |  SORT AGGREGATE           |      |     1 |    66 |          |      |
17 |   2 |   BITMAP CONVERSION COUNT     |      |    12 |   792 |   263   (0)| 00:00:04 |
18 |*  3 |    BITMAP INDEX FAST FULL SCAN| IND_BMAP |     |     |          |      |
19 ------------------------------------------------------------------------------------------
20 
21 Predicate Information (identified by operation id):
22 ---------------------------------------------------
23 
24    3 - filter("T_BMAP"."OBJECT_NAME" IS NOT NULL AND "T_BMAP"."OBJECT_NAME" LIKE
25           '%ObjectStreamClass%')
26 
27 Note
28 -----
29    - dynamic sampling used for this statement (level=2)
30 
31 
32 Statistics
33 ----------------------------------------------------------
34      28  recursive calls
35       0  db block gets
36     360  consistent gets
37     591  physical reads
38       0  redo size
39     422  bytes sent via SQL*Net to client
40     419  bytes received via SQL*Net from client
41       2  SQL*Net roundtrips to/from client
42       0  sorts (memory)
43       0  sorts (disk)
44       1  rows processed
45 
46 SQL> select count(*) from t_bmap where t_bmap.object_name like '%ObjectStreamClass%';
47 
48   COUNT(*)
49 ----------
50     84
51 
52 
53 Execution Plan
54 ----------------------------------------------------------
55 Plan hash value: 891302759
56 
57 ------------------------------------------------------------------------------------------
58 | Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
59 ------------------------------------------------------------------------------------------
60 |   0 | SELECT STATEMENT          |      |     1 |    66 |   263   (0)| 00:00:04 |
61 |   1 |  SORT AGGREGATE           |      |     1 |    66 |          |      |
62 |   2 |   BITMAP CONVERSION COUNT     |      |    12 |   792 |   263   (0)| 00:00:04 |
63 |*  3 |    BITMAP INDEX FAST FULL SCAN| IND_BMAP |     |     |          |      |
64 ------------------------------------------------------------------------------------------
65 
66 Predicate Information (identified by operation id):
67 ---------------------------------------------------
68 
69    3 - filter("T_BMAP"."OBJECT_NAME" IS NOT NULL AND "T_BMAP"."OBJECT_NAME" LIKE
70           '%ObjectStreamClass%')
71 
72 Note
73 -----
74    - dynamic sampling used for this statement (level=2)
75 
76 
77 Statistics
78 ----------------------------------------------------------
79       0  recursive calls
80       0  db block gets
81     285  consistent gets
82       0  physical reads
83       0  redo size
84     422  bytes sent via SQL*Net to client
85     419  bytes received via SQL*Net from client
86       2  SQL*Net roundtrips to/from client
87       0  sorts (memory)
88       0  sorts (disk)
89       1  rows processed

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

1 SQL> create table t_all as select * from dba_objects;
2 
3 Table created.
4 
5 SQL> create index ind_all on t_all(object_name) indextype is ctxsys.context;
6 
7 Index created.

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

 1 SQL> select count(*) from t_all where contains(t_all.object_name,'%ObjectStreamClass%')>0;
 2 
 3   COUNT(*)
 4 ----------
 5     84
 6 
 7 
 8 Execution Plan
 9 ----------------------------------------------------------
10 Plan hash value: 3532980284
11 
12 ----------------------------------------------------------------------------
13 | Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)| Time       |
14 ----------------------------------------------------------------------------
15 |   0 | SELECT STATEMENT |       |     1 |    78 |     4   (0)| 00:00:01 |
16 |   1 |  SORT AGGREGATE  |       |     1 |    78 |        |       |
17 |*  2 |   DOMAIN INDEX     | IND_ALL |    35 |  2730 |     4   (0)| 00:00:01 |
18 ----------------------------------------------------------------------------
19 
20 Predicate Information (identified by operation id):
21 ---------------------------------------------------
22 
23    2 - access("CTXSYS"."CONTAINS"("T_ALL"."OBJECT_NAME",'%ObjectStreamCl
24           ass%')>0)
25 
26 Note
27 -----
28    - dynamic sampling used for this statement (level=2)
29 
30 
31 Statistics
32 ----------------------------------------------------------
33        2221  recursive calls
34       0  db block gets
35        2228  consistent gets
36     267  physical reads
37       0  redo size
38     422  bytes sent via SQL*Net to client
39     419  bytes received via SQL*Net from client
40       2  SQL*Net roundtrips to/from client
41     254  sorts (memory)
42       0  sorts (disk)
43       1  rows processed
44 
45 SQL> select count(*) from t_all where contains(t_all.object_name,'%ObjectStreamClass%')>0;
46 
47   COUNT(*)
48 ----------
49     84
50 
51 
52 Execution Plan
53 ----------------------------------------------------------
54 Plan hash value: 3532980284
55 
56 ----------------------------------------------------------------------------
57 | Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)| Time       |
58 ----------------------------------------------------------------------------
59 |   0 | SELECT STATEMENT |       |     1 |    78 |     4   (0)| 00:00:01 |
60 |   1 |  SORT AGGREGATE  |       |     1 |    78 |        |       |
61 |*  2 |   DOMAIN INDEX     | IND_ALL |    35 |  2730 |     4   (0)| 00:00:01 |
62 ----------------------------------------------------------------------------
63 
64 Predicate Information (identified by operation id):
65 ---------------------------------------------------
66 
67    2 - access("CTXSYS"."CONTAINS"("T_ALL"."OBJECT_NAME",'%ObjectStreamCl
68           ass%')>0)
69 
70 Note
71 -----
72    - dynamic sampling used for this statement (level=2)
73 
74 
75 Statistics
76 ----------------------------------------------------------
77      48  recursive calls
78       0  db block gets
79     348  consistent gets
80       0  physical reads
81       0  redo size
82     422  bytes sent via SQL*Net to client
83     419  bytes received via SQL*Net from client
84       2  SQL*Net roundtrips to/from client
85       0  sorts (memory)
86       0  sorts (disk)
87       1  rows processed
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/Richardzhu/p/2831221.html