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

is

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