Oracle full-text search

Using like '%', Oracle performs a full table scan, which consumes a considerable amount of system resources. This allows you to use full-text retrieval of oracle to increase the query speed.

1. SQL>Select*FromV $ versionWhereRownum = 1;
3. BANNER
4. ----------------------------------------------------------------
5. OracleDatabase10g Enterprise Edition Release 10.2.0.1.0-Prod
7. SQL>Create TableT (textVarchar(4000 ));
9. The table has been created.
11. SQL>Insert IntoTValues('You love her so much, why don't you leave her');
13. One row has been created.
15. SQL>BeginCtx_ddl.create_preference ('Club _ lexer','Chinese _ vgram_lexer');End
16. 2/
18. The PL/SQL process is successfully completed.
20. SQL>Create IndexIdx_t_textOnT (text) indextypeIs
21. 2 ctxsys. context parameters ('Lexer club_lexer');
23. The index has been created.
25. SQL>Select Count(*)FromTWhere Contains(Text,'Who')> 0;
26. Select Count(*)FromTWhere Contains(Text,'Who')> 0
27. *
28. Row 3 has an error:
29. ORA-29902: An error occurred while executing the ODCIIndexStart () Routine
30. ORA-20000: Oracle Text error:
31. DRG-10817:CONTAINSA phrase in which a search term contains a disabled word or a disabled word: What
34. SQL>Select Count(*)FromTWhere Contains(Text,'Who')> 0;
36. COUNT(*)
37. ----------
38. 1
40. SQL>Select Count(*)FromTWhere Contains(Text,'Ta')> 0;
42. COUNT(*)
43. ----------
44. 1
46. SQL>Select Count(*)FromTWhere Contains(Text,'Then')> 0;
48. COUNT(*)
49. ----------
50. 1

Regularly synchronize and optimize Indexes

1. SQL>Create Or Replace ProcedureHsp_sync_indexAs
2. 2Begin
3. 3 ctx_ddl.sync_index ('Idx _ t_text');
4. 4End;
5. 5/
7. The process has been created.
9. SQL>-- Regularly synchronize Indexes
10. SQL> VARIABLE jobno number;
11. SQL>BEGIN
12. 2 DBMS_JOB.SUBMIT (: jobno,'Children _ sync_index ();',
13. 3 SYSDATE,'Sysdate + (1/24/4 )');
14. 4Commit;
15. 5END;
16. 6/
18. The PL/SQL process is successfully completed.
20. SQL>Create Or Replace ProcedureHsp_optimize_indexAs
21. 2Begin
22. 3 ctx_ddl.optimize_index ('Idx _ t_text','Full');
23. 4End;
24. 5/
26. The process has been created.
28. SQL>-- Regularly optimize Indexes
29. SQL> VARIABLE jobno number;
30. SQL>BEGIN
31. 2 DBMS_JOB.SUBMIT (: jobno,'Heat _ optimize_index ();',
32. 3 SYSDATE,'Sysdate + 1');
33. 4Commit;
34. 5END;
35. 6/
37. The PL/SQL process is successfully completed.

Before creating a synchronization and optimized storage process, www.bkjia.com should use the sys user to grant permissions to evan users: grant execute on ctx_ddl to evan;
Otherwise, the following message will be reported:
SQL> create or replace procedure hsp_sync_index
2 begin
3 ctx_ddl.sync_index ('idx _ t_text ');
4 end;
5/
Warning: the creation process has a compilation error.
SQL> show errors
PROCEDURE heat _ sync_index error:
LINE/COL ERROR
-------------------------------------------------------------------------
3/5 PL/SQL: Statement ignored
3/5 PLS-00201: The identifier 'ctx _ ddler' must be declared'
In addition, pay special attention to forbidden words.