Oracle full-text index optimization like

Oracle full-text index optimization like a recent service, which was originally called, but a new problem has emerged. Two LIKE '% XXX %' are found, and this is heartbroken, the table record is about million now, full table scan, your sister's. In fact, this kind of SQL does not actually need to be changed to the service, and you just need to go back and rewrite it. However, I can only optimize it from my side now. I asked about development, but I can only perform Full fuzzy queries. Neither '% XXX' nor 'xxx %' can work, 'xxx % 'we all know that indexes can be used in general.' % XXX' is actually a good optimization. You can use reverse and then think about it again, using REGEXP_LIKE? Use instr ????, None, because the thing in the center of the percentage sign is a variable. How can this be done? Think about it or use full-text indexing to see how it works. After the experiment is completed, the effect is acceptable. The rough steps are as follows: conn/as sysdba @? /Ctx/admin/catctx. SQL ctxsys tbs_ctxsys temp unlock specifies the username, tablespace, and temporary tablespace, and unlocks the creation of ctxsys user grant execute on ctx_ddl to yypt; conn yypt/partition ('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 '); If a DRG-10700 error occurs during index creation, see login with CTXSYS user @? /Ctx/admin/defaults/dr0defin. SQL "AMERICAN"; SQL> explain plan for SELECT. AUTHOR, 2. ID BOOK_ID, 3. NAME BOOK_NAME, 4. success, 5. AN_NAME, 6. IS_USE, 7. 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. ID = B. BOOKID (+) 12 AND A.S _ LEVEL <= 2 13 and. BOOKSTATE = 1 14 AND (. keyword like '% %' or. name like '% %' or. AUTHOR LIKE '% %') 15 order by num DESC; Explained. elapsed: 00:00:00. 01SQL> select * from table (dbms_xplan.display); PLAN_TABLE_OUTPUT partition Plan hash value: 2129267741 rows | Id | Operation | Name | Ro Ws | Bytes | Cost (% CPU) | Time | Bytes | 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 AC Cess 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 | identified by operation id: --------------------------------------------------------------- 3-filter ("". "BOOKSTATE" = 1 AND ("". "KEYWORD" LIKE '% Three Kingdoms %' OR "". "NAME" LIKE '% %' OR "". "AUTHOR" LIKE '% %') AND "". "S_LEVEL" <= 2) 5-access ("". "ID" = "B ". "BOOKID" (+) SQL> explain plan for SELECT. AUTHOR, 2. ID BOOK_ID, 3. NAME BOOK_NAME, 4. success, 5. AN_NAME, 6. IS_USE, 7. 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. ID = B. BOOKID (+) 12 AND A.S _ LEVEL <= 2 13 and. BOOK STATE = 1 14 AND (contains (. KEYWORD, 'three kingdoms ')> 0 OR contains (. name, 'three kingdoms ')> 0 OR contains (. author, 'three kingdoms ')> 0) 15 order by num DESC; Explained. elapsed: 00:00:00. 10SQL> select * from table (dbms_xplan.display); PLAN_TABLE_OUTPUT partition Plan hash value: 4091324736 -------------------------------------- Role | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | percent | 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 IN DEX | 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 | ------------------------------------------------- Required Predicate Information (identified by operation id): --------------------------------------------------- 3-filter ("". "BOOKSTATE" = 1 AND "". "S_LEVEL" <= 2) 8-access ("CTXSYS ". "CONTAINS" ("". "KEYWORD", 'three kingdoms ')> 0 AND "CTXSYS ". "CONTAINS" ("". "NAME", 'three kingdoms ')> 0 AND "CTXSYS ". "CONTAINS" ("". "AUTHOR", 'three kingdoms ')> 0) 11-access ("CTXSYS ". "CONTAINS" ("". "NAME", 'san Country)> 0 AND "CTXSYS ". "CONTAINS" ("". "AUTHOR", 'three kingdoms ')> 0) 14-access ("CTXSYS ". "CONTAINS" ("". "AUTHOR", 'three kingdoms ')> 0) 16-access ("". "ID" = "B ". "BOOKID" (+) ignores full-text index synchronization and optimization, because it is only a test, because the table where the full-text index is located has done any DML, but the index is not automatically maintained, therefore, only one JOB can be manually written for synchronization and maintenance. Lexical analyzer: chinese_lexer Chinese analyzer. It only supports UTF8 and chinese_vgram_lexer for Chinese analyzer. basic_lexer is mainly used for English. It is recommended that you do not use it for Chinese search. The created Oracle Text index is called domain index, which includes four index types: l CONTEXT, 2 CTXCAT, 3 CTXRULE, and 4 CTXXPATH. Use synchronization and optimization indexes as needed: create or replace procedure merge ('indexname); ctx_ddl.optimize_index ('indexname', 'full'); end; this is very simple. To explain, ctx_ddl.sync_index is used to save new information, optimize the condition, it is to delete invalid old information, reduce fragments, and write a JOB. When it is blank, execute the process and then introduce the full-text composite index for creating multiple fields: SQL> conn ctxsys/ctxsys SQL> EXEC commands ('yypt _ idx_subject ', 'Multi _ column_datastore'); SQL> EXEC ctx_ddl.set_attribute ('yypt _ idx_subject', 'columns ', 'keyword, name, author'); ------ SQL> conn yypt/yyptSQL> 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 ');