oracle全文索引优化like
最近一个业务，本来调的差不多了，但是新问题又来了，发现两条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_