Oracle Text Index-part6-preference and stoplist

前面的part1,part2,part3,part4,part5中都是使用的Oracle的默认的基础全文索引创建index和谓词查询,但是实际的数据可能会包含一下特殊字符或者间隔符等,比如我们想将”_”这个连接符跟两端的单词一起作为一个TOKEN的时候,就需要更高级一点的语法了。
本次实验中删除部分初始化数据中的中文记录所在的行,目前创建完INDEX后的TOKEN信息如下:

文中的环境信息为Oracle 11.2.0.4

SQL> select token_text from DR$IDX1_T_LEXER$I t;

TOKEN_TEXT
----------------------------------------------------------------
14895956868
515.124.4169
650.121.2019
BERRY
BLUE
CHINA
CITY
FIVE
FUZZY
INDEX
JOHN
LEO
MINUTES
NANJING
PETER
SHANGHAI
SMITH
TESTING
TEXT
TOMAS
UNDER

TOKEN信息中记录的单词都是被拆开的单词,但是对于其中的一些记录,比如Under_Smith,我们是希望作为一个独立的单词存在并被直接检索出来的,对于这种情况,普通创建Text Index的语法就不能很好的满足需求了,这个时候我们可以借助Oracle提供的CTX_DDL的其他子存储过程或者函数来更细致的定制索引的TOKEN拆分规则。

我们使用基本的lexer,也就是basic_lexer。首先创建一个preference叫做mylex来使用basic_lexer,然后设置一个attribute叫做printjoins来处理“_-”连接符。
语法如下

DROP INDEX IDX1_T_LEXER;
begin
ctx_ddl.create_preference('mylex', 'basic_lexer');
ctx_ddl.set_attribute('mylex', 'printjoins', '_-');
end;
/
CREATE INDEX IDX1_T_LEXER ON T_LEXER(DATA_ECHO_AREA) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('LEXER MYLEX SYNC(ON COMMIT)');

执行过程输出
SQL> DROP INDEX IDX1_T_LEXER;

索引已删除。

已用时间:  00: 00: 00.03
SQL>
SQL>
SQL>
SQL> begin
  2  ctx_ddl.create_preference('mylex', 'basic_lexer');
  3  ctx_ddl.set_attribute('mylex', 'printjoins', '_-');
  4  end;
  5  /

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.09
SQL>
SQL>
SQL> CREATE INDEX IDX1_T_LEXER ON T_LEXER(DATA_ECHO_AREA) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('LEXER MYLEX SYNC(ON COMMIT)');

索引已创建。

已用时间:  00: 00: 01.14

此时的TOKEN信息

SQL> select token_text from DR$IDX1_T_LEXER$I t;

TOKEN_TEXT
----------------------------------------------------------------
14895956868
515.124.4169
650.121.2019
BERRY
BLUE
CHINA
CITY
FIVE
FUZZY
INDEX
JOHN
LEO
MINUTES
NANJING
PETER
SHANGHAI
SMITH
SMITH-SMITH   --此处’-’生效
TESTING
TEXT
TOMAS
UNDER_SMITH   --下划线作为TOKEN显示出来

此时查询Under_smith就可以直接查询到

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

 SCORE(99)         ID CREATE_DATE         AREA
---------- ---------- ------------------- ------------------------------
         7         12 2016-12-28 10:47:58 Under_Smith

已用时间:  00: 00: 00.02
SQL> @x
Display execution plan for last statement for this session from library cache...

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dc94zdqu5g80n, child number 0
-------------------------------------
select score(99), id, create_date, dbms_lob.substr(DATA_ECHO_AREA) AREA
 from T_LEXER   where contains(DATA_ECHO_AREA, 'under_smith', 99) > 0
order by score(99) desc

Plan hash value: 3809827892

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | E-Rows |E-Bytes| Cost (%CPU)| Pstart| Pstop |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |        |       |     5 (100)|       |       |       |       |          |
|   1 |  SORT ORDER BY                      |              |      1 |    98 |     5  (20)|       |       |  2048 |  2048 | 2048  (0)|
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| T_LEXER      |      1 |    98 |     4   (0)| ROWID | ROWID |       |       |          |
|*  3 |    DOMAIN INDEX                     | IDX1_T_LEXER |        |       |     4   (0)|       |       |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------

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

   3 - access("CTXSYS"."CONTAINS"("DATA_ECHO_AREA",'under_smith',99)>0)

对于现实中的数据,很多单词或者字母没有实际的业务含义,这样的信息如果作为TOKEN提供搜索并没有意义并且会占据大量的空间,对查询的效率也会有一定的影响。

比如加载下面这样的数据

SQL> insert into t_lexer
  2    (id, create_date, DATA_ECHO_AREA)
  3    values
  4    (seq_t_lexer.nextval,
  5      sysdate,
  6      '--------test stoplist and dashes------
  7      select id,create_date,data_echo_area from t_lexer;');

已创建 1 行。

已用时间:  00: 00: 00.00
SQL> commit;

提交完成。
SQL> insert into t_lexer
  2    (id, create_date, DATA_ECHO_AREA)
  3    values
  4    (seq_t_lexer.nextval,
  5      sysdate,
  6      '-----------------------------');

已创建 1 行。

已用时间:  00: 00: 00.06

SQL> commit;
提交完成。

这个时候TOKEN信息表DR$IDX1_T_LEXER$I中记录的信息包含了SELECT,”—–”这样的信息,这样的TOKEN信息在业务场景下是没有意义的,这个时候我们可以借助与CTX_DDL中的create_stoplist过程来添加不需要创建TOKEN的字符串来达到过滤的目的。
在测试的过程中对于SQL文本中带”——”这种注释的情况,会发生与partjoin冲突造成添加的这项stoplist不生效。
下面是脚本中追加了stoplist内容

exec ctx_ddl.drop_preference ('mylex');
exec ctx_ddl.drop_stoplist('my_stoplist');
DROP INDEX IDX1_T_LEXER;
begin
ctx_ddl.create_preference('mylex', 'basic_lexer');
ctx_ddl.set_attribute('mylex', 'printjoins', '_');
ctx_ddl.create_stoplist('my_stoplist');
ctx_ddl.add_stopword('my_stoplist', '---');
ctx_ddl.add_stopword('my_stoplist', 'select');
ctx_ddl.add_stopword('my_stoplist', 'from');
ctx_ddl.add_stopword('my_stoplist', 'and');
ctx_ddl.add_stopword('my_stoplist', 'not');
ctx_ddl.add_stopword('my_stoplist', 'or');
end;
/
CREATE INDEX IDX1_T_LEXER ON T_LEXER(DATA_ECHO_AREA) INDEXTYPE IS CTXSYS.CONTEXT 
PARAMETERS('LEXER MYLEX STOPLIST MY_STOPLIST SYNC(ON COMMIT)');

此时查询TOKEN信息,这些STOPLIST将不会再显示。
此时表中的数据和TOKEN的信息

SQL> select id, create_date, dbms_lob.substr(DATA_ECHO_AREA) AREA from T_LEXER t;

        ID CREATE_DATE         AREA
---------- ------------------- ------------------------------------------------------------------------------------------
        12 2016-12-28 10:47:58 Under_Smith
        11 2016-12-28 10:41:41 Smith Blue
         3 2016-12-28 10:32:03 Shanghai is a city in the CHINA
         5 2016-12-28 10:32:03 Nanjing is a city in the CHINA
         6 2016-12-28 10:32:03 515.124.4169
         7 2016-12-28 10:32:03 14895956868
         8 2016-12-28 10:32:03 Peter Smith-Smith
         9 2016-12-28 10:32:03 John Smith
        10 2016-12-28 10:32:03 650.121.2019
       103 2017-01-23 15:04:36 --------test stoplist and dashes------
                                   select id,create_date,data_echo_area from t_lexer;

       104 2017-01-23 15:16:11 -----------------------------
        41 2017-01-10 12:53:30 Berry Blue
        61 2017-01-10 13:14:14 and
        62 2017-01-10 13:15:45 tomas and leo
        63 2017-01-10 13:16:28 and or fuzzy not
        81 2017-01-22 14:45:45 Text Index Testing
        82 2017-01-22 15:02:39 Five Minutes

已选择17行。

已用时间:  00: 00: 00.06
SQL> select token_text from DR$IDX1_T_LEXER$I t;

TOKEN_TEXT
----------------------------------------------------------------
14895956868
515.124.4169
650.121.2019
A
BERRY
BLUE
CHINA
CITY
CREATE_DATE
DASHES
DATA_ECHO_AREA
FIVE
FUZZY
ID
IN
INDEX
IS
JOHN
LEO
MINUTES
NANJING
PETER
SHANGHAI
SMITH
STOPLIST
TEST
TESTING
TEXT
THE
TOMAS
T_LEXER
UNDER_SMITH

已选择32行。

更多的定制请查看Oracle关于的官方手册关于CTX_DDL部分的内容。

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

发表评论

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