Research on full-text retrieval in Oracle)

3.10 common scripts

3.10.1. Delete preference:

Begin

Ctx_ddl.drop_preference (my_lexer );

End;

 

3.10.2. Index reconstruction:

Alter index newsindex rebuild parameters (replace lexer my_lexer );

 

3.10.3 synchronize Indexes

Begin

Ctx_ddl.sync_index (myindex, 2 M );

End;

Or set the synchronization script in the background:

$ ORACLE_HOME/ctx/sample/script/drjobdml. SQL -- background synchronization script (in 9i, 10 Gb does not know where to put it. The document has a problem)

SQL> @ drjobdml myindex 360 -- the index is synchronized for 360 minutes in a cycle.

Or by creating indexes and adding Parameters

-- Indicates synchronization every hour, with a memory of 16 Mb.

Create index IND_m_high ON my_high (DOCS) indextype is ctxsys. CONTEXT

Parameters (sync (EVERY "TRUNC (SYSDATE) + 1/24") memory 16 m) parallel 2 online;

(Confirm that the index synchronization can be completed within this interval. Otherwise, the sync job will be suspended)

-- Or yes

Sync (manual) -- manual synchronization, default

Sync (on commit) -- synchronize immediately after dml

-- Define synchronization through job

Declare

Job number;

Begin

Dbms_job.submit (job,

Ctx_ddl.sync_index (ind_m_high);, -- index name

Interval => SYSDATE + 1/1440); -- once every 1 minute

Commit;

Dbms_output.put_line (job | has been submitted .);

End;

 

 

3.10.4. Index fragmentation:

After the index is created, DOG may be the following entry:

DOG DOC1 DOC3 DOC5

After the new document is added to the table, the new document will be synchronized to the index. If the DOG in Doc7 in the new document is synchronized to the index

It may become the following entry

DOG DOC1 DOC3 DOC5

DOG DOC7

Subsequent DML operations may become:

DOG DOC1 DOC3 DOC5

DOG DOC7

DOG DOC9

DOG DOC11

This results in fragmentation and requires index optimization.

View index fragments

Create table output (result CLOB );

Declare

X clob: = null;

Begin

Ctx_report.index_stats (idx_auction, x );

Insert into output values (x );

Commit;

Dbms_lob.freetemporary (x );

End;

Select * from output

 

3.10.5 index optimization:

The difference between quick fast optimization and full optimization is that quick optimization does not delete useless and expired data, while full deletes old data (deleted rows)

-- Rapid Optimization

Begin

Ctx_ddl.optimize_index (myidx, FAST );

End;

-- All Optimization

Begin

Ctx_ddl.optimize_index (myidx, FULL );

End;

-- Optimizes a single mark. The default mode is full.

Begin

Ctx_ddl.optimize_index (myidx, token, TOKEN => Oracle );

End;

 

3.10.6.Online parameter restrictions:

At the very beginning or very end of this process, dml might fail.

Online is supported for context indexes only.

Online cannot be used with parallel.

-- Parameters must be added after the online Index; otherwise, it will fail.

Alter index IND_m_high rebuild online parameters (sync memory 16 m)

 

3.10.7. Change the index attributes without re-Indexing

Replaces the existing preference class settings, including SYNC parameters, of the index

The settings from new_preference. Only index preferences and attributes are replaced. The index is

Not rebuilt.

Alter index myidx rebuild parameters (replace metadata transactional );

Alter index idx_auction_db1 rebuild PARAMETERS (replace metadata sync (on commit ));

3.10.8.Score:

-- Indicates the score. The higher the score, the more accurate the data is.

Select score (1), id, text FROM docs where contains (text, oracle, 1)> 0;

-- Sort by score

Select score (1), title from news where contains (text, oracle, 1)> 0 AND issue_date> =

(01-OCT-97)

Order by score (1) DESC;

 

3.10.9. analysis table:

Analyze table <table_name> compute statistics;

Analyze table <table_name> estimate statistics 1000 ROWS;

Analyze table <table_name> estimate statistics 50 PERCENT;

Analyze table <table_name> delete statistics;

 

3.10.10. Data dictionary table:

View the system's default oracle text Parameters

Select pre_name, pre_object from ctx_preferences

After the dml operation is queried, the index is synchronized.

SELECT pnd_index_name, pnd_rowid, to_char (pnd_timestamp, dd-mon-yyyy hh24: mi: ss)

Timestamp FROM ctx_user_pending;

View error log table

Select * from CTX_USER_INDEX_ERRORS

 

3.10.11.Php, Jsp application oracle text:

Http://download.oracle.com/docs/cd/B19306_01/text.102/b14217/acase.htm

 

3.10.12. logical operators:

 

-- Or operator

Select id, text from docs where contains (text, city or state)> 0;

-- And Operator

Select id, text from docs where contains (text, city and state)> 0;

Or

Select id, text from docs where contains (text, city state)> 0;

 

 

3.10.13. INDEX OPTIMIZATION PROBLEMS

-- First look at an example

SQL> exec ctx_ddl.optimize_index (idx_auction_db1, FULL );

PL/SQL procedure successfully completed.

Elapsed: 00:16:16. 77

Index optimization is quite slow. It takes less than five minutes to create a context index for 2 million of data, but it takes 16 minutes to optimize the index.

The optimization speed is very unacceptable for data with hundreds of millions of tables. At first, I thought this was a bug in oracle, and I found it later.

Some documents. Oracle10g introduced rebuild optimization parameters, and the speed was still very fast.

SQL> exec ctx_ddl.optimize_index (idx_auction_db1, rebuild );

PL/SQL procedure successfully completed.

 

 

3.10.14 transaction Query

Indexes may not be synchronized in real time, but queries must be performed in real time.

-- Change the index to transactional. When you query the index again, the dr $ unindexed table will be used to find records that meet the conditions that have not been indexed.

Alter index idx_auction_db1 rebuild parameters (replace metadata transactional)

Example:

Select count (*) from table where contains (text, someword)> 0; -- 0 hits

Insert into table values (someword );

Select count (*) from table where contains (text, someword)> 0; -- 1 hit (the one we just

Entered)

Rollback;

Select count (*) from table where contains (text, someword)> 0; -- 0 hit

Only enable a session

Exec ctx_query.disable_transactional_query: = TRUE;