本文共 20202 字，大约阅读时间需要 67 分钟。
《oracle分区与索引》
引言：oracle的分区和索引可以说是它自己的亮点，可能你会说在其他数据库上也有，嗯是的，但oracle的种类 性能 便利性可以说是比较人性化的，下面我们通过实验来阐述它们的特性和功能。
1.分别给出一个B-tree索引针对全表扫描性能高和低的例子。
索引定义：oracle数据库中索引就是为了加快数据访问速度的一种目录结构
B-tree索引特点：
（1）二叉树结构
（2）用比较大小方式查找索引块
（3）适合创建在键值重复率低的字段
例如 主键字段：强调表的参照关系，即可以被外键引用
唯一性约束字段：强调字段键值的唯一性
（4）第一次扫描时，从root根节点进入，后面就不在返回进入了
（5）叶子与叶子之间有指针链，不用返回上一层，可以直接定位到下一个叶子节点
（6）主键字段做搜索时效率与数据量无关，例如 1万条记录 1亿条记录检索效率差不多
（7）索引块是按顺序存放的，数据块是打散存放的
（8）结果集越小性能越好，结果集越大性能越不好
（9）相比位图索引，占用空间较多
实验
LEO1@LEO1> drop table leo1;
Table dropped.
LEO1@LEO1> drop table leo2;
Table dropped.
先清理环境，我们重新创建表和索引，看看在不同执行计划下的性能如何。
LEO1@LEO1> create table leo1 as select * from dba_objects;
Table created.
我们创建leo1表用于全表扫描
LEO1@LEO1> create table leo2 as select * from dba_objects;
Table created.
我们创建leo2表用于走B-tree索引
LEO1@LEO1> create index idx_leo2 on leo2(object_id);
Index created.
LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO1',cascade=>true);
PL/SQL procedure successfully completed.
LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO2',cascade=>true);
PL/SQL procedure successfully completed.
对leo1和leo2表及表上的索引进行统计分析，以便让oracle了解它们的数据分布情况
LEO1@LEO1> select table_name, num_rows, last_analyzed, object_type from dba_tab_statistics where owner = 'LEO1';
TABLE_NAME NUM_ROWS LAST_ANAL OBJECT_TYPE
------------------------------ ---------- --------- -------------------------------- ---------- --------- -------
LEO1 71961 09-JAN-13 TABLE
LEO2 71962 09-JAN-13 TABLE
好已经显示出对2张表进行统计分析了，并且还知道了表上的数据有71961行和71962行
LEO1@LEO1> set autotrace traceonly 启动执行计划
LEO1@LEO1> select * from leo1 where object_id=10000;
Execution Plan
----------------------------------------------------------
Plan hash value: 2716644435
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 287 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL | LEO1 | 1 | 97 | 287 (1)| 00:00:04 |
--------------------------------------------------------------------------
走全表扫描，代价Cost=287
Predicate Information (identified by operation id):
---------------------------------------------------------------------------
1 - filter("OBJECT_ID"=10000) 谓词条件
Statistics
---------------------------------------------------------------------------
1 recursive calls
0 db block gets
1031 consistent gets 1031个一致性读
1026 physical reads
0 redo size
1626 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LEO1@LEO1> select * from leo2 where object_id=10000;
Execution Plan
----------------------------------------------------------
Plan hash value: 2495991774
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | LEO2 | 1 | 97 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_LEO2 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
走B-tree索引，代价Cost=2
Predicate Information (identified by operation id):
----------------------------------------------------------------------------------------
2 - access("OBJECT_ID"=10000) 谓词条件
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets 4个一致性读=先访问root->在找branch->在找leaf+递归IO
0 physical reads
0 redo size
1629 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我们从上面的执行计划可以看出，走B-tree索引效率要比全表扫描高出很多很多，尤其在键值重复率低的字段非常适合使用B-tree索引（流程：先访问root->在找branch->在找leaf->在找到键值key->访问对应ROWID数据块->提取数据），我们还要知道当结果集越小使用索引访问速度越快，如果结果集较大那么，我们看看性能如何呢？
LEO1@LEO1> select * from leo1 where object_id>=10000;
62253 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2716644435
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 62216 | 5893K| 287 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL | LEO1 | 62216 | 5893K| 287 (1)| 00:00:04 |
--------------------------------------------------------------------------
走全表扫描，代价Cost=287
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID">=10000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5118 consistent gets 5118个一致性读
0 physical reads
0 redo size
3245084 bytes sent via SQL*Net to client
46174 bytes received via SQL*Net from client
4152 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
62253 rows processed
LEO1@LEO1> select /*+ index(leo2 idx_leo2) */ * from leo2 where object_id>=10000;
62254 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2495991774
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 62217 | 5893K| 1073 (1)| 00:00:13 |
| 1 | TABLE ACCESS BY INDEX ROWID| LEO2 | 62217 | 5893K| 1073 (1)| 00:00:13 |
|* 2 | INDEX RANGE SCAN | IDX_LEO2 | 62217 | | 139 (0)| 00:00:02 |
----------------------------------------------------------------------------------------
走B-tree索引，代价Cost=1073
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=10000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9312 consistent gets 哇塞居然9312个一致性读
49 physical reads
0 redo size
7232860 bytes sent via SQL*Net to client
46174 bytes received via SQL*Net from client
4152 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
62254 rows processed
我用hint方式强制走索引，很明显索引的效率大大低于全表扫描，看来CBO的判断还是正确的，因为会出现这种情况呢，走索引的原理是先访问一次索引块，在访问一次数据块，这样便至少是2次IO，当你查询结果集越大时，消耗IO资源就越多（一致性读次数就越多），所以呢还不如直接访问表效率高，你这时很聪明，可能会问结果集多大时，索引效率就不好了呢，一般超过总量1/5时效率就会变差，这只是一个经验值，大家要多多测试出真知。
2.分别给出一个Bitmap索引针对b-tree索引性能高和低的例子。
Bitmap索引特点：
（1）键值行结构
（2）使用位图标识键值
（3）适合创建在键值重复率高的字段
（4）键值重复率越高，占用的空间越少，每个独立键值占用一行
（5）适合OLAP系统
（6）DML操作会锁定整个位图索引段，导致阻塞和无法大并发
（7）位运算效率非常高，例如 and or not 运算
实验
注：我的实验要结合上下文一起看哦，有的表或者对象会在上面的实验中创建
LEO1@LEO1> create table leo3 as select * from dba_objects; 创建leo3与leo2表结构一样
Table created.
LEO1@LEO1> create bitmap index idx_leo3 on leo3(object_id); 创建bitmap索引
Index created.
LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO3',cascade=>true); 统计分析
PL/SQL procedure successfully completed.
LEO1@LEO1> set autotrace off;
LEO1@LEO1> select table_name, num_rows, last_analyzed, object_type from dba_tab_statistics where owner = 'LEO1';
TABLE_NAME NUM_ROWS LAST_ANAL OBJECT_TYPE
------------------------------ ---------- --------- ------------
LEO1 71961 09-JAN-13 TABLE
LEO2 71962 09-JAN-13 TABLE
LEO3 71964 09-JAN-13 TABLE
LEO1@LEO1> select count(*) from leo2 where object_id>10000;
Execution Plan
----------------------------------------------------------
Plan hash value: 788375040
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 45 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX FAST FULL SCAN | IDX_LEO2 | 62216 | 303K| 45 (0)| 00:00:01 |
----------------------------------------------------------------------------------
快速索引全扫描，把索引链分割成若干区域，多索引块并行扫描，所以很快，Cost=45
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID">10000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
167 consistent gets 167个一致性读，一次IO读取多个块
1 physical reads
0 redo size
528 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LEO1@LEO1> select count(*) from leo3 where object_id>10000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1835111598
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 218 (0)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | BITMAP CONVERSION COUNT | | 62218 | 303K| 218 (0)| 00:00:03 |
|* 3 | BITMAP INDEX RANGE SCAN | IDX_LEO3 | | | | |
--------------------------------------------------------------------------------------
位图索引范围扫描->位图值转换成统计值->集合排序，Cost=218
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID">10000)
filter("OBJECT_ID">10000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
219 consistent gets 219个一致性读
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LEO1@LEO1> select segment_name,extents,blocks from dba_segments where segment_name in ('IDX_LEO4','IDX_LEO5');
SEGMENT_NAME EXTENTS BLOCKS
IDX_LEO2 B-tree索引 17 256
IDX_LEO3 Bitmap索引 18 384
因为B-tree索引和Bitmap索引组成结构不同，在Bitmap索引中每个独立键值占用一行，我们知道object_id是没有重复值的，所以组成Bitmap索引时每个object_id键值都占用一行，因此就比B-tree索引占用的索引块多，占用的索引块多扫描的就多一致性IO就多效率就低，总而言之，Bitmap索引适合重复率高的字段
LEO1@LEO1> create table leo4 as select * from dba_objects; 创建leo4
Table created.
LEO1@LEO1> create table leo5 as select * from dba_objects; 创建leo5
Table created.
LEO1@LEO1> create index idx_leo4 on leo4(object_type); 创建B-tree
Index created.
LEO1@LEO1> create bitmap index idx_leo5 on leo5(object_type); 创建Bitmap
Index created.
LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO4',cascade=>true); 统计分析
PL/SQL procedure successfully completed.
LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO5',cascade=>true); 统计分析
PL/SQL procedure successfully completed.
LEO1@LEO1> select table_name, num_rows, last_analyzed, object_type from dba_tab_statistics where owner = 'LEO1';
TABLE_NAME NUM_ROWS LAST_ANAL OBJECT_TYPE
------------------------------ ---------- --------- ------------
LEO1 71961 09-JAN-13 TABLE
LEO2 71962 09-JAN-13 TABLE
LEO3 71964 09-JAN-13 TABLE
LEO4 71966 09-JAN-13 TABLE
LEO5 71967 09-JAN-13 TABLE
LEO1@LEO1> select * from leo4 where object_type='TABLE';
2807 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 412720909
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1674 | 158K| 75 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | LEO4 | 1674 | 158K| 75 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_LEO4 | 1674 | | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
走B-tree索引范围扫描，Cost=75
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
99 recursive calls
0 db block gets
568 consistent gets 568个一致性读
0 physical reads
0 redo size
312088 bytes sent via SQL*Net to client
2581 bytes received via SQL*Net from client
189 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
2807 rows processed
LEO1@LEO1> select * from leo5 where object_type='TABLE';
2808 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 174753293
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1674 | 158K| 203 (0)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID | LEO5 | 1674 | 158K| 203 (0)| 00:00:03 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | IDX_LEO5 | | | | |
-----------------------------------------------------------------------------------------
走Bitmap索引
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
355 consistent gets 355个一致性读
0 physical reads
0 redo size
312171 bytes sent via SQL*Net to client
2581 bytes received via SQL*Net from client
189 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2808 rows processed
LEO1@LEO1> select segment_name,extents,blocks from dba_segments where segment_name in ('IDX_LEO4','IDX_LEO5');
SEGMENT_NAME EXTENTS BLOCKS
IDX_LEO4 17 256
IDX_LEO5 2 16
如上所示两个索引各自占用空间情况，下面我们来分析一下，在键值重复率高情况下为什么位图索引效率好？
不出我们所料Bitmap索引比B-tree索引效率要高哦，正好中了键值重复率越高，占用的空间越少这句话，因为object_type字段有很多重复键值，我们在查询过程中只对object_type='TABLE'的“键值行”扫描一遍即可知道哪些记录符合条件，从占用索引块数量上也能看出扫描16个块要比扫描256个块快大发啦！哈哈
3.演示DML操作导致位图索引锁定位图段示例
Bitmap锁定特点：
（1）当我们操作同一个“键值行”时，会产生锁定整个键值行
（2）所以不建议频繁进行DML操作
（3）适合OLAP系统 例如 报表生成 位运算 统计分析
实验
LEO1@LEO1> select distinct sid from v$mystat; 显示当前会话id，用于区别其他会话操作
SID
----------------------
133
会话133
LEO1@LEO1> create table leo6 (id int,name varchar2(20)); 创建leo6表，2个字段
Table created.
LEO1@LEO1> create bitmap index idx_leo6 on leo6(id); 在id字段上创建位图索引
Index created.
LEO1@LEO1> insert into leo6 values(1,'leo'); 插入记录
1 row created.
LEO1@LEO1> insert into leo6 values(1,'sun'); 插入记录
1 row created.
LEO1@LEO1> commit; 提交
Commit complete.
LEO1@LEO1> select * from leo6; 显示数据
ID NAME
----------------------------
1 leo
1 sun
Id列的值相同，从Bitmap索引结构上看，这两条记录都是在同一个键值行上，我们如果操作其中的一条记录那么就是对这个键值行操作
LEO1@LEO1> update leo6 set id=2 where name='leo'; 更新一条记录
1 row updated.
会话157，重新打开一个会话
LEO1@LEO1> update leo6 set id=2 where name='sun';
这时你会惊讶的发现光标不动了，命令提示符也不能显示出来了，怎么回事，想一想你是不是在哪里碰到过这种情况，对这就是传说中的“阻塞”现象，是被第133会话给阻塞了，而我们原来碰到的是当2个会话同时修改同一条记录时会发生阻塞，而现在我们更新的是2条不同记录为什么也会发生阻塞呢，一位伟人说过“存在即合理”，那么既然发生了，就必然会有关系，只是这种关系与记录无关，而是发生在索引键值行上。
这就是Bitmap索引的一个特性：DML操作会锁定整个位图段（键值行）导致阻塞现象，这是因为oracle为了保证数据一致性和完整性，必须将索引键值行锁定，防止其他会话对其修改，归根结底这都是由于位图索引的构造原理造成的，一个键值行对应多条记录，当其中任意记录值被修改时，oracle会锁定整个键值行信息，此时另一个会话如果要修改这个键值行中的记录时，这个操作就会被之前的操作所阻塞。
解决方案：要么commit/rollback 133会话，要么终止157会话
我们从v$lock视图中看一下锁定情况
LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (133,157);
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ------------ -- ---------- ---------- ---------- ----------
133 AE 100 0 4 0 0
157 AE 100 0 4 0 0
133 TO 65927 1 3 0 0
133 TO 5003 1 3 0 0
157 TM 73837 0 3 0 0
133 TM 73837 0 3 0 0
157 TX 589827 1307 6 0 0
133 TX 131088 1284 6 0 1
157 TX 131088 1284 0 4 0
SID：会话id
ID1+ID2：修改数据块上记录地址
LMODE：持有的锁类型
REQUEST：正在请求的锁
BLOCK：阻塞会话个数
说明：133会话正在持有一个6级TX事务锁（排他锁）并且正好阻塞另一个会话，从ID1+ID2地址上看133会话恰恰正在阻塞157会话，而157会话目前没有锁正在请求一个4级TX事务锁，只有133会话提交后才会释放6级TX锁
133会话
LEO1@LEO1> commit;
Commit complete.
LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (133,157);
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
133 AE 100 0 4 0 0
157 AE 100 0 4 0 0
133 TO 65927 1 3 0 0
133 TO 5003 1 3 0 0
157 TM 73837 0 3 0 0
157 TX 589827 1307 6 0 0
此时133会话的6级TX锁已经被释放了，157会话阻塞解除可以继续操作并获得一个6级锁
4.创建一个全文索引（Text index），比较它和传统的模糊查询的性能。
全文索引特点：
（1）使用字符串拆字方法检索“字 词 短语”，适合文本搜索
（2）场景在用文本模糊查询时，使用全文索引比较高效 例 where name like ‘%leo%’
（3）我们目的找到所有包含“leo”字符串的记录，由于leo字符串在字段中的位置是不固定的，使用B-tree索引就没有办法进行检索，而使用全文索引就可以很好按照拆分字的原理进行检索
（4）全文索引是一个逻辑名，内部包含许多基表和基索引，它们实际占用空间，而全文索引名不占用空间不是段对象，这些内部对象组成了全文索引
（5）全文索引占用空间大，一般全文索引是基表大小1.5倍
（6）管理维护成本高，bug多
实验
LEO1@LEO1> create table leo7 (id number,name varchar2(20)); 创建leo7表
Table created.
LEO1@LEO1> create index idx_leo7 on leo7(name); 在name字段创建B-tree索引
Index created.
LEO1@LEO1> insert into leo7 values (1,'leo');
1 row created.
LEO1@LEO1> insert into leo7 values (2,'leo leo');
1 row created.
LEO1@LEO1> insert into leo7 values (3,'leo leo leo'); 插入3条记录
1 row created.
LEO1@LEO1> commit; 提交
Commit complete.
LEO1@LEO1> select * from leo7; 显示有3条
ID NAME
---------- --------------------
1 leo
2 leo leo
3 leo leo leo
LEO1@LEO1> create table leo8 as select * from leo7; 我们通过leo7创建leo8表
Table created.
LEO1@LEO1> create index idx_text_leo8 on leo8(name) indextype is ctxsys.context;
Index created.
注：我们在创建oracle 11g全文索引时等待时间较长，大约8秒钟，为什么会这么长时间呢，它又做了哪些内容呢？
（1）先检查oracle是否已安装“全文检索工具”，oracle11g 默认安装的，oracle10g默认没有安装
（2）再进行“语法分析”就是我们上面提到的“拆字”过程，例如创建词法分析器及相关表等操作
如果你的是oracle10g数据库，请参考《oracle10g 不能成功创建全文索引的解决方法》http://f.dataguru.cn/thread-49019-1-1.html
LEO1@LEO1> set autotrace on 显示执行计划
LEO1@LEO1> select * from leo7 where name like '%leo%';
ID NAME
---------- --------------------
1 leo
2 leo leo
3 leo leo leo
Execution Plan
----------------------------------------------------------
Plan hash value: 598568836
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 75 | 0 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LEO7 | 3 | 75 | 0 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | IDX_LEO7 | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
走的是索引全扫描，当数据量小的时候还可以，大了就差些了
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NAME" IS NOT NULL AND "NAME" LIKE '%leo%') 执行计划重写了谓词条件，因为它发现这样更高效
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
12 consistent gets 12个一致性读
0 physical reads
0 redo size
680 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
LEO1@LEO1> select * from leo8 where contains(name,'leo')>0;
ID NAME
---------- --------------------
1 leo
2 leo leo
3 leo leo leo
Execution Plan
----------------------------------------------------------
Plan hash value: 287112382
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | LEO8 | 1 | 37 | 4 (0)| 00:00:01 |
|* 2 | DOMAIN INDEX | IDX_TEXT_LEO8 | | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
域索引就是全文索引，可能会有当第一次执行的时候效率较低，多执行几次后效率就提高了
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CTXSYS"."CONTAINS"("NAME",'leo')>0)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
11 recursive calls
0 db block gets
11 consistent gets 11个一致性读
0 physical reads
0 redo size
680 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
小结：使用全文索引的时候，当检索数据量大时 短语组成较复杂时效率较好。
待接一楼回复
转载地址：http://lpsra.baihongyu.com/