Oracle Text Index-part2

环境

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分类目录。将固定链接加入收藏夹。

2 Responses to Oracle Text Index-part2

  1. Pingback引用通告: Oracle Text Index-part3 | LEO Notes

  2. Pingback引用通告: Oracle Text Index-part6-preference and stoplist | LEO Notes

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s