oracle全文索引优化like(二)
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_optimiz