本文讨论了一个业务中遇到的问题，通过引入全文索引优化模糊查询性能，包括创建索引步骤、优化过程和使用正则表达式、REGEXP_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
文章结束给大家分享下程序员的一些笑话语录： 一个合格的程序员是不会写出 诸如 “摧毁地球” 这样的程序的，他们会写一个函数叫 “摧毁行星”而把地球当一个参数传进去。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
