对CLOB字段执行全文检索的比较

来源：未知责任编辑：责任编辑发表时间:2013-08-27 15:59　点击:次

    sns.t_news 表的content_txt 字段为 CLOB类型，共3683行数据。分析见下：
--1.使用like：
SQL> select * from sns.t_news t where t.content_txt like '%广东%';
执行计划
| Id | Operation         | Name   | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     6 | 7662 |    11   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_NEWS |     6 | 7662 |    11   (0)| 00:00:01 |
----------------------------------------------------------------------------
统计信息
          3 recursive calls
          0 db block gets
       5092 consistent gets
       2525 physical reads
          0 redo size
   10517580 bytes sent via SQL*Net to client
    7056514 bytes received via SQL*Net from client
       6110 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
        906 rows processed

--2. 使用dbms_lob.instr：
SQL> select * from sns.t_news t where dbms_lob.instr(t.content_txt,'广东',1,1) > 0;
执行计划
| Id | Operation         | Name   | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     7 | 8939 |    10   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_NEWS |     7 | 8939 |    10   (0)| 00:00:01 |
----------------------------------------------------------------------------
统计信息
        656 recursive calls
          0 db block gets
       6358 consistent gets
       2548 physical reads
          0 redo size
       其余同上
--3. 使用 instr函数：
SQL> select * from sns.t_news t where instr(t.content_txt,'广东',1,1) > 0;
执行计划
| Id | Operation         | Name   | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     7 | 8939 |    11   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_NEWS |     7 | 8939 |    11   (0)| 00:00:01 |
----------------------------------------------------------------------------
统计信息
          1 recursive calls
          0 db block gets
       5091 consistent gets
       2525 physical reads
          0 redo size
    其余同上

--4. 使用域索引：
--建域索引使用92.5秒
SQL> CREATE INDEX idx_content_txt ON sns.t_news(content_txt) INDEXTYPE IS CTXSYS.CONTEXT;
SQL> select * from sns.t_news t WHERE contains (t.content_txt, '广东') > 0;
执行计划
| Id | Operation                   | Name          | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 | 1277 |     4(0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| T_NEWS         |     1 | 1277 |     4(0)| 00:00:01 |
|* 2 | DOMAIN INDEX              | IDX_CONTENT_TXT |       |       |     4(0)| 00:00:01 |
--------------------------------------------------------------------------------
统计信息
         11 recursive calls
          0 db block gets
       3064 consistent gets
       2054 physical reads
          0 redo size
   其余同上

--直观的比较：
                      like   dbms_lob.instr    instr     contains
recursive calls       3       656               1           11
db block gets         0         0               0            0
consistent gets    5092      6358            5091         3064
physical reads     2525      2548            2525         2054
redo size             0         0               0            0
     总结不用说了，上面的一目了然，顺便提下测试时要从远程数据库取数据，用下面的语句报错
select * from t_news@SNS.US.ORACLE.COM t;
ORA-22992: 无法使用从远程表选择的 LOB 定位符
--使用下面的可以
create table t_news_back as select * from t_news@SNS.US.ORACLE.COM t;

--注释：统计列         解释
     db block gets      从buffer cache中读取的block的数量
     consistent gets    从 buffer cache中读取的undo数据的block的数量
     physical reads     从磁盘读取的block的数量
     redo size          DML生成的redo的大小
     sorts (memory)     在内存执行的排序量
     sorts (disk)       在磁盘上执行的排序量
     recursive calls    递归调用