Performance Advantage Instances for Oracle Full-text indexing

First, the experiment shows:

Operating system: Rhel 5.4 x86

Database: Oracle 11g R2

Second, the operation steps:

2.1, first create a table T_btree, and create a B-tree index, the index key is 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.

Next, execute the following query statement two times:

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
----------------------------------------------------------
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. Create the table T_bmap and create the bitmap index:

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.

Execute the same statement query as before:

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
----------------------------------------------------------
Recursive calls
0 db Block gets
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. Create a table t_all and create a full-text index:

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.

The following changes the query statement, but the output needs to be the same as before:

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

Performance Advantage Instances for Oracle Full-text indexing