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

灰太狼 2021-11-01 06:40 160阅读 0赞

一、实验说明：

操作系统：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/archive/2012/12/24/2831221.html