一、实验说明： 操作系统：rhel 5.4 x86 数据库：oracle 11g r2 二、操作步骤： 2.1、首先创建一个表t_btree，并创建
首页 → 数据库技术
背景：
阅读新闻
oracle全文索引的性能优势实例
[日期：2014-10-08]来源：linux公社 作者：linux[字体：]
一、实验说明：
操作系统：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 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、创建表t_bmap，并创建bitmap索引：
t_bmap
sql
6 index created。
执行之前的同样的语句查询：
sql> select count(*) from t_bmap where t_bmap.object_name like '%objectstreamclass%';
count(*)
----------
84
execution plan
----------------------------------------------------------
plan hash value: 891302759