oracle全文索引优化like


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_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               |                       |