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 processedSQL> 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 processed2.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 processedSQL> 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本文永久更新链接地址