Example of Oracle full-text index performance advantages

Example of Oracle full-text index performance advantages

1. Experiment description:

Operating System: rhel 5.4x86

Database: Oracle 11g R2

Ii. Procedure:

2.1 create a t_btree table 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.

Run the following query statement twice:

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

Bytes -----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -----------------------------------------------------------------------------------
| 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 |
Bytes -----------------------------------------------------------------------------------

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 cballs
0 db block gets
454 consistent gets
726 physical reads
0 redo size
422 bytes sent via SQL * Net to client
419 bytes encoded ed 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

Bytes -----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -----------------------------------------------------------------------------------
| 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 |
Bytes -----------------------------------------------------------------------------------

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 cballs
0 db block gets
369 consistent gets
0 physical reads
0 redo size
422 bytes sent via SQL * Net to client
419 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

2.2 create a t_bmap table and create a 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.

Query the same statement before execution:

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

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

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

Bytes ------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ------------------------------------------------------------------------------------------
| 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 |
Bytes ------------------------------------------------------------------------------------------

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 cballs
0 db block gets
360 consistent gets
591 physical reads
0 redo size
422 bytes sent via SQL * Net to client
419 bytes encoded ed 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

Bytes ------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ------------------------------------------------------------------------------------------
| 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 |
Bytes ------------------------------------------------------------------------------------------

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 cballs
0 db block gets
285 consistent gets
0 physical reads
0 redo size
422 bytes sent via SQL * Net to client
419 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

This article permanently updates the link address:

1 2 Next Page