Re: Oracle Text and indexes

Text indexing has changed a lot between 8.1.6 and 10.2.0

If you insert text after the index was created it will not be indexed until you synchronise the index.

Here are some useful queries

select * from ctxsys.CTX_PENDING order by 1,2;

• You must be logged on as the owner to do this -- exec ctx_ddl.optimize_index('SRCH_SKILL_INDEX','FAST'); ctx_ddl.sync_index('SRCH_COURSE_CD_INDEX','20M');
• Show how many records need indexing select pnd_index_name, count(*) from ctxsys.CTX_PENDING group by pnd_index_name order by pnd_index_name;
• Indexes that need optimizing, ie they are over 10 select 'SELECT '''||owner||'.'||table_name||''' the_object, round(AVG(COUNT(*)),1) Should_be_less_than_10 FROM '||owner||'.'||table_name||' GROUP BY TOKEN_TEXT HAVING COUNT(*) > 1 union ' from dba_tables where table_name like 'DR$%$I';

select * from ctx_parameters ;

• How do you empty CTX_INDEX_ERRORS view?

truncate table ctxsys.dr$index_error;

RicercatoreSbadato wrote:
> But How can I know when the index is indexed ?
>
> U have inserted some test and after you have create the index. But if
> you
>
> 1. create the index
> 2. insert some text
>
> How can you know if the text you have inserted is indexed ?
>
Received on Mon Jun 26 2006 - 01:48:10 CDT