Oracle Text Index-part2

发表于12月 28, 2016由Leo

环境

Oracle 11.2.0.4 字符集AL32UTF8

脚本

创建securefile CLOB分区，disable storage in row模式。

CREATE TABLE t_lexer(
ID                NUMBER(10)       NOT NULL,
CREATE_DATE       DATE             NOT NULL,
DATA_ECHO_AREA    CLOB             NOT NULL
)
LOB(DATA_ECHO_AREA) store as securefile
(disable storage in row)
tablespace lexer
LOGGING
PCTFREE 10
INITRANS 100
PARTITION BY RANGE (CREATE_DATE)
(PARTITION P2016_12 VALUES LESS THAN (TO_DATE('01-01-2017', 'MM-DD-YYYY')),
PARTITION P2017_01 VALUES LESS THAN (TO_DATE('02-01-2017', 'MM-DD-YYYY')),
PARTITION P2017_02 VALUES LESS THAN (TO_DATE('03-01-2017', 'MM-DD-YYYY')),
PARTITION PMAX VALUES LESS THAN (MAXVALUE));

alter table t_lexer add(
constraint pk_t_lexer primary key(id)
);

create sequence seq_t_lexer;

初始化数据

insert into t_lexer
  (id, create_date, DATA_ECHO_AREA)
values
  (seq_t_lexer.nextval,
   sysdate,
   '故事发生在19世纪末的欧洲，善良可爱的苏菲被恶毒的女巫施下魔咒，从18岁的女孩变成90岁的婆婆，孤单无助的她无意中走入镇外的移动城堡，据说它的主人哈尔以吸取
女孩的灵魂为乐，但是事情并没有人们传说的那么可怕，性情古怪的
哈尔居然收留了苏菲，两个人在四脚的移动城堡中开始了奇妙的共同生活，一段交织了爱与痛、乐与悲的爱情故事在战火中悄悄展开');

insert into t_lexer
  (id, create_date, DATA_ECHO_AREA)
values
  (seq_t_lexer.nextval,
   sysdate,
   '这部由俄罗斯导演提莫贝克曼贝托夫执导的影片自6月末在北美上映以来，已经在全球取得了超过3亿美元的票房收入。在亚洲上映后也先后拿下日本、韩国等地的票房冠军宝座。
虽然不少网友在此之前也相继通过各种渠道接触到本片，但相信影片凭着在大银幕上呈现出的超酷的视听效果，依然能够吸引大量影迷前往影院捧场');

insert into t_lexer
  (id, create_date, DATA_ECHO_AREA)
values
  (seq_t_lexer.nextval,
   sysdate,
   'California is a state in the US');
   
insert into t_lexer
  (id, create_date, DATA_ECHO_AREA)
values
  (seq_t_lexer.nextval,
   sysdate,
   '运维:/indexpage/indexpagesActionGetTopPage.action?pageType=0,1,2,3,5,10');

insert into t_lexer
 (id, create_date, DATA_ECHO_AREA)
 values
 (seq_t_lexer.nextval,
   sysdate,
   'steven king');

insert into t_lexer
 (id, create_date, DATA_ECHO_AREA)
 values
 (seq_t_lexer.nextval,
   sysdate,
   '515.124.4169');

insert into t_lexer
 (id, create_date, DATA_ECHO_AREA)
 values
 (seq_t_lexer.nextval,
   sysdate,
   '14895956868');

insert into t_lexer
 (id, create_date, DATA_ECHO_AREA)
 values
 (seq_t_lexer.nextval,
   sysdate,
   'Peter Smith-Smith');

insert into t_lexer
 (id, create_date, DATA_ECHO_AREA)
 values
 (seq_t_lexer.nextval,
   sysdate,
   'John Smith');

insert into t_lexer
 (id, create_date, DATA_ECHO_AREA)
 values
 (seq_t_lexer.nextval,
   sysdate,
   '650.121.2019');

insert into t_lexer
 (id, create_date, DATA_ECHO_AREA)
 values
 (seq_t_lexer.nextval,
   sysdate,
   'Smith Blue');

insert into t_lexer
 (id, create_date, DATA_ECHO_AREA)
 values
 (seq_t_lexer.nextval,
   sysdate,
   'Under_Smith');
commit;

收集统计信息

begin dbms_stats.gather_table_stats(
user,
't_lexer',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/

现在我们想获取DATA_ECHO_AREA字段中包含SMITH的行，我们使用的语句会是模糊查询like ‘%SMITH%’

SQL> col area for a30
SQL> select id,create_date,dbms_lob.substr(DATA_ECHO_AREA) AREA from T_LEXER  where upper(DATA_ECHO_AREA) like '%SMITH%';

        ID CREATE_DATE         AREA
---------- ------------------- ------------------------------
        12 2016-12-28 10:47:58 Under_Smith
        11 2016-12-28 10:41:41 Smith Blue
         8 2016-12-28 10:32:03 Peter Smith-Smith
         9 2016-12-28 10:32:03 John Smith
-------------------------------------------------------------------------------------
| Id  | Operation           | Name    | E-Rows |E-Bytes| Cost (%CPU)| Pstart| Pstop |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |        |       |    15 (100)|       |       |
|   1 |  PARTITION RANGE ALL|         |      1 |    98 |    15   (0)|     1 |     4 |
|*  2 |   TABLE ACCESS FULL | T_LEXER |      1 |    98 |    15   (0)|     1 |     4 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(UPPER("DATA_ECHO_AREA") LIKE '%SMITH%')

当然也可以使用INSTR这个Oracle提供的函数来进行处理，速度会比like快，因为是经过ORACLE优化过的。

SQL> select id,create_date,dbms_lob.substr(DATA_ECHO_AREA) AREA from T_LEXER  where instr(upper(DATA_ECHO_AREA),'SMITH') > 0;

        ID CREATE_DATE         AREA
---------- ------------------- ------------------------------
        12 2016-12-28 10:47:58 Under_Smith
        11 2016-12-28 10:41:41 SmithBlue
         8 2016-12-28 10:32:03 Peter Smith-Smith
         9 2016-12-28 10:32:03 John Smith
-------------------------------------------------------------------------------------
| Id  | Operation           | Name    | E-Rows |E-Bytes| Cost (%CPU)| Pstart| Pstop |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |        |       |    15 (100)|       |       |
|   1 |  PARTITION RANGE ALL|         |      1 |    98 |    15   (0)|     1 |     4 |
|*  2 |   TABLE ACCESS FULL | T_LEXER |      1 |    98 |    15   (0)|     1 |     4 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(INSTR(UPPER("DATA_ECHO_AREA"),'SMITH')>0)

但是问题来了， DATA_ECHO_AREA没有索引，Oracle需要逐行去扫描记录来匹配，如果CLOB记录成千上万，那么速度是相当慢的，由此查询引起的 DB CACHE的问题也会一起到来。
我们再看下数据，无论是何种情况下的SMITH都会被查询出来。

如果我们使用Oracle的Text Index会如何呢？接下来我们采用ctxsys.context索引类型创建全文索引。

create index IDX1_T_LEXER ON T_LEXER(DATA_ECHO_AREA) INDEXTYPE IS CTXSYS.CONTEXT;

当全文索引创建完成之后，Oracle会在user_tables和user_index下创建一些以DR为前缀的数据库对象，并且后缀也有不同的命名规则，这些名字是强制不能修改的。

SQL> SELECT table_name FROM USER_TABLES where table_name like '%IDX1_T_LEXER%';
TABLE_NAME
------------------------------------------------------------
DR$IDX1_T_LEXER$I
DR$IDX1_T_LEXER$R
DR$IDX1_T_LEXER$K
DR$IDX1_T_LEXER$N

使用全文索引的检索方式来进行对SMITH词进行检索

SQL> select id, create_date, dbms_lob.substr(DATA_ECHO_AREA) AREA
  2    from T_LEXER
  3   where contains(DATA_ECHO_AREA,'SMITH') > 0;  --查询默认是不区分大小写的，小写的结果也是一样。

        ID CREATE_DATE         AREA
---------- ------------------- ------------------------------
        12 2016-12-28 10:47:58 Under_Smith
        11 2016-12-28 10:41:41 Smith Blue
         8 2016-12-28 10:32:03 Peter Smith-Smith
         9 2016-12-28 10:32:03 John Smith
---------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name         | E-Rows |E-Bytes| Cost (%CPU)| Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |              |        |       |     4 (100)|       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T_LEXER      |      1 |    98 |     4   (0)| ROWID | ROWID |
|*  2 |   DOMAIN INDEX                     | IDX1_T_LEXER |        |       |     4   (0)|       |       |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CTXSYS"."CONTAINS"("DATA_ECHO_AREA",'SMITH')>0)

从执行计划上看到查询使用了DOMAIN INDEX，成本从15减少到4.

CONTAINS操作符

CONTAINS操作符只能对全文索引的列进行使用，查询的字符串前后不需要通配符，Oracle用”score”来返回contains的结果来标识有匹配记录还是没有匹配记录。
使用score方式表示的查询和结果如下

SQL> select score(100), id, create_date, dbms_lob.substr(DATA_ECHO_AREA) AREA
  2    from T_LEXER
  3   where contains(DATA_ECHO_AREA, 'SMITH',100) > 0
  4   order by score(100) desc;

SCORE(100)         ID CREATE_DATE         AREA
---------- ---------- ------------------- ------------------------------
         9          8 2016-12-28 10:32:03 Peter Smith-Smith
         4          9 2016-12-28 10:32:03 John Smith
         4         12 2016-12-28 10:47:58 Under_Smith
         4         11 2016-12-28 10:41:41 Smith Blue

score的得分并没有绝对的意义，上述的结果只能说明ID=8的结果比其他结果得到的score高，因为出现了2次smith。

此条目发表在index, Uncategorized分类目录。将固定链接加入收藏夹。

1 Responses to Oracle Text Index-part2

  1. Pingback： Oracle Text Index-part3 | LEO Notes

留下评论