全文索引的作用 oracle,Oracle全文索引的性能优势实例

一、实验说明： 操作系统：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

本文原创发布php中文网，转载请注明出处，感谢您的尊重！