oracle 全文索引优化like

来源：互联网发布：阿里云幕布多少钱编辑：程序博客网时间：2024/05/16 09:04

最近一个业务，本来调的差不多了，但是新问题又来了，发现两条LIKE '%XXX%',看到这个，心碎了，表记录现在大约11W吧，全表扫描啊，你妹的，这种SQL其实业务就不改让上，直接打回去重写好了。

可是，现在只能从我这边做优化了，问了问开发，只能是完全模糊查询，'%xxx'和'xxx%'都不行啊，'xxx%'大家知道，一般都可以用到索引，'%xxx'这个其实也好优化，用reverse呗，之后又想了想，用正则REGEXP_LIKE？用instr？？？？，都不行，因为百分号中间那玩意是个变量。这可如何是好，想想还是用全文索引看看如何吧，完了做了把实验，效果还可以。

粗略步骤如下：

conn /as sysdba

@?/ctx/admin/catctx.sql ctxsys tbs_ctxsys temp unlock

这里指定了用户名，表空间，临时表空间，并且解锁

创建ctxsys用户

grant execute on ctx_ddl to yypt;
conn yypt/xxxxxxxxx

begin
ctx_ddl.create_preference ('YYPT_CHINESE_LEXER', 'chinese_vgram_lexer');
end;
/

exec ctx_ddl.drop_preference('YYPT_CHINESE_LEXER');

CREATE INDEX IDX_bookinfo_keyword ON book_info(keyword)
INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('LEXER YYPT_CHINESE_LEXER');

CREATE INDEX IDX_bookinfo_name ON book_info(name)
INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('LEXER YYPT_CHINESE_LEXER');

CREATE INDEX IDX_bookinfo_author ON book_info(author)
INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('LEXER YYPT_CHINESE_LEXER');

这里如果在创建索引的时候出现DRG-10700错误，请参考一下

用CTXSYS用户登陆
@?/ctx/admin/defaults/dr0defin.sql "AMERICAN";

SQL> explain plan for SELECT A.AUTHOR,
2         A.ID BOOK_ID,
3         A.NAME BOOK_NAME,
4         A.FEE,
5         A.AN_NAME,
6         A.IS_USE,
7         A.BOOK_STYLE,
8   DECODE(b.sum,NULL,0,b.sum) num
9    FROM BOOK_INFO A, BOOK_HISTORY_DOWNLOAD B
10   WHERE 1 = 1
11     AND A.ID = B.BOOKID(+)
12     AND A.S_LEVEL <= 2
13     AND A.BOOKSTATE = 1
14     AND (A.KEYWORD LIKE '%三国%' OR A.NAME LIKE '%三国%' OR A.AUTHOR LIKE '%三国%')
15   ORDER BY num DESC;

Explained.

Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2129267741

-------------------------------------------------------------------------------------------------------
| Id | Operation                     | Name                  | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                       | 3866 |   400K| 6528   (1)| 00:01:19 |
|   1 | SORT ORDER BY                |                       | 3866 |   400K| 6528   (1)| 00:01:19 |
|   2 |   NESTED LOOPS OUTER          |                       | 3866 |   400K| 6527   (1)| 00:01:19 |
|* 3 |    TABLE ACCESS FULL          | BOOK_INFO             | 3866 |   366K| 1442   (1)| 00:00:18 |
|   4 |    TABLE ACCESS BY INDEX ROWID| BOOK_HISTORY_DOWNLOAD |     1 |     9 |     3   (0)| 00:00:01 |
|* 5 |     INDEX RANGE SCAN          | IDX_HISTORY_DOWNLOAD |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

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

   3 - filter("A"."BOOKSTATE"=1 AND ("A"."KEYWORD" LIKE '%三国%' OR "A"."NAME" LIKE '%三国%' OR
              "A"."AUTHOR" LIKE '%三国%') AND "A"."S_LEVEL"<=2)
   5 - access("A"."ID"="B"."BOOKID"(+))

SQL> explain plan for SELECT A.AUTHOR,
2         A.ID BOOK_ID,
3         A.NAME BOOK_NAME,
4         A.FEE,
5         A.AN_NAME,
6         A.IS_USE,
7         A.BOOK_STYLE,
8   DECODE(b.sum,NULL,0,b.sum) num
9    FROM BOOK_INFO A, BOOK_HISTORY_DOWNLOAD B
10   WHERE 1 = 1
11     AND A.ID = B.BOOKID(+)
12     AND A.S_LEVEL <= 2
13     AND A.BOOKSTATE = 1
14     AND (contains(A.KEYWORD,'三国')>0 OR contains(A.name,'三国')>0 OR contains(A.author,'三国')>0)
15   ORDER BY num DESC;

Explained.

Elapsed: 00:00:00.10
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4091324736

------------------------------------------------------------------------------------------------------------
| Id | Operation                          | Name                  | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                       |    41 | 4838 |   110   (1)| 00:00:02 |
|   1 | SORT ORDER BY                     |                       |    41 | 4838 |   110   (1)| 00:00:02 |
|   2 |   NESTED LOOPS OUTER               |                       |    41 | 4838 |   109   (0)| 00:00:02 |
|* 3 |    TABLE ACCESS BY INDEX ROWID     | BOOK_INFO             |    41 | 4469 |    55   (0)| 00:00:01 |
|   4 |     BITMAP CONVERSION TO ROWIDS    |                       |       |       |            |          |
|   5 |      BITMAP OR                     |                       |       |       |            |          |
|   6 |       BITMAP CONVERSION FROM ROWIDS|                       |       |       |            |          |
|   7 |        SORT ORDER BY               |                       |       |       |            |          |
|* 8 |         DOMAIN INDEX               | IDX_BOOKINFO_KEYWORD |       |       |     4   (0)| 00:00:01 |
|   9 |       BITMAP CONVERSION FROM ROWIDS|                       |       |       |            |          |
| 10 |        SORT ORDER BY               |                       |       |       |            |          |
|* 11 |         DOMAIN INDEX               | IDX_BOOKINFO_NAME     |       |       |     4   (0)| 00:00:01 |
| 12 |       BITMAP CONVERSION FROM ROWIDS|                       |       |       |            |          |
| 13 |        SORT ORDER BY               |                       |       |       |            |          |
|* 14 |         DOMAIN INDEX               | IDX_BOOKINFO_AUTHOR   |       |       |     4   (0)| 00:00:01 |
| 15 |    TABLE ACCESS BY INDEX ROWID     | BOOK_HISTORY_DOWNLOAD |     1 |     9 |     3   (0)| 00:00:01 |
|* 16 |     INDEX RANGE SCAN               | IDX_HISTORY_DOWNLOAD |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

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

   3 - filter("A"."BOOKSTATE"=1 AND "A"."S_LEVEL"<=2)
   8 - access("CTXSYS"."CONTAINS"("A"."KEYWORD",'三国')>0 AND "CTXSYS"."CONTAINS"("A"."NAME",'三国')>0
              AND "CTXSYS"."CONTAINS"("A"."AUTHOR",'三国')>0)
11 - access("CTXSYS"."CONTAINS"("A"."NAME",'三国')>0 AND "CTXSYS"."CONTAINS"("A"."AUTHOR",'三国')>0)
14 - access("CTXSYS"."CONTAINS"("A"."AUTHOR",'三国')>0)
16 - access("A"."ID"="B"."BOOKID"(+))

以上忽略了全文索引的同步和优化，因为只是测试，因为全文索引的所在表做了任何DML，索引可是不会自动维护的，所以只能手动写个JOB同步和维护了。

词法分析器：chinese_lexer汉语分析器，只支持UTF8，chinese_vgram_lexer针对汉语的分析器，basic_lexer主要用于英文，中文检索建议还是不要用了。

建立的Oracle Text索引被称为域索引(domain index)，包括4种索引类型：

l CONTEXT，2 CTXCAT，3 CTXRULE，4 CTXXPATH根据需求使用

同步和优化索引：

create or replace procedure text_index_sync_optimizer

begin

ctx_ddl.sync_index('indexname);

ctx_ddl.optimize_index('indexname','FULL');

end;

这个很简单，解释一下，ctx_ddl.sync_index同步，也就是保存新信息，ctx_ddl.optimize_index优化，就是删除无效的旧信息，减少碎片，完了写个JOB，看啥时候空就执行一下过程即可

另外再介绍一下创建多个字段的全文组合索引：

SQL> conn ctxsys/ctxsys
SQL> EXEC ctx_ddl.create_preference('YYPT_idx_subject','MULTI_COLUMN_DATASTORE');

SQL> EXEC ctx_ddl.set_attribute('YYPT_idx_subject','columns','keyword,name,author'); ------

SQL> conn yypt/yypt

SQL>exec ctx_ddl.create_preference ('YYPT_CHINESE_LEXER', 'chinese_lexer');
SQL> CREATE INDEX ctx_idx_subject ON book_info(keyword)
INDEXTYPE IS ctxsys.CONTEXT PARAMETERS('DATASTORE ctxsys.YYPT_idx_subject lexer YYPT_CHINESE_LEXER');

详细可可以查看下YTK的http://blog.itpub.net/post/7268/484444