Forum OpenACS Q&A: please help me "documents are not getting executed"

SQL> select ctx_report.create_index_script ('search_web_index') from dual;
begin
ctx_ddl.create_preference('"SEARCH_WEB_INDEX_DST"','DIRECT_DATASTORE');
end;
/

begin
ctx_ddl.create_preference('"SEARCH_WEB_INDEX_FIL"','INSO_FILTER');
end;
/

begin
ctx_ddl.create_section_group('"SEARCH_WEB_INDEX_SGP"','HTML_SECTION_GROUP');
end;
/

begin
ctx_ddl.create_preference('"SEARCH_WEB_INDEX_LEX"','BASIC_LEXER');
end;
/

begin
ctx_ddl.create_preference('"SEARCH_WEB_INDEX_WDL"','BASIC_WORDLIST');
ctx_ddl.set_attribute('"SEARCH_WEB_INDEX_WDL"','STEMMER','ENGLISH');
ctx_ddl.set_attribute('"SEARCH_WEB_INDEX_WDL"','FUZZY_MATCH','GENERIC');
end;
/

begin
ctx_ddl.create_stoplist('"SEARCH_WEB_INDEX_SPL"','BASIC_STOPLIST');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','a');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','about');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','after');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','all');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','also');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','an');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','and');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','any');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','are');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','as');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','at');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','be');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','because');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','been');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','but');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','by');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','can');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','co');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','corp');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','could');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','do');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','for');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','from');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','had');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','has');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','have');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','he');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','her');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','his');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','i');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','if');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','in');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','inc');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','into');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','is');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','it');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','its');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','last');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','may');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','more');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','most');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','mr');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','mrs');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','ms');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','mz');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','no');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','not');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','of');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','on');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','one');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','only');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','or');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','other');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','out');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','over');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','police');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','s');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','sapol');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','says');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','section');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','she');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','should');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','so');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','some');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','such');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','than');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','that');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','the');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','their');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','there');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','they');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','this');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','to');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','up');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','was');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','we');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','were');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','what');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','when');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','where');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','which');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','who');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','why');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','will');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','with');
ctx_ddl.add_stopword('"SEARCH_WEB_INDEX_SPL"','would');
end;
/

begin
ctx_ddl.create_preference('"SEARCH_WEB_INDEX_STO"','BASIC_STORAGE');
ctx_ddl.set_attribute('"SEARCH_WEB_INDEX_STO"','R_TABLE_CLAUSE','lob (data) st
ore as (cache)');
ctx_ddl.set_attribute('"SEARCH_WEB_INDEX_STO"','I_INDEX_CLAUSE','compress 2');
end;
/

begin
ctx_output.start_log('SEARCH_WEB_INDEX_LOG');
end;
/

create index "SAPOL_CMS"."SEARCH_WEB_INDEX"
on "SAPOL_CMS"."SEARCH_WEB"
("SEARCH_CONTENT")
indextype is ctxsys.context
parameters('
datastore "SEARCH_WEB_INDEX_DST"
filter "SEARCH_WEB_INDEX_FIL"
section group "SEARCH_WEB_INDEX_SGP"
lexer "SEARCH_WEB_INDEX_LEX"
wordlist "SEARCH_WEB_INDEX_WDL"
stoplist "SEARCH_WEB_INDEX_SPL"
storage "SEARCH_WEB_INDEX_STO"
')
/

begin
ctx_output.end_log;
end;
/

This index used to work fine for quite some time, but now when try to resync by typing the ctx_ddl.sync_index directly into SQLPlus it will not pickup new documents. However, we did find that dropping the index and recreating it again index the new documents.

Basically,
1) we add a document to the table
2) run ctx_ddl.sync_index
3) perform a search on this index/table and the document is never returned

Inserted some word and pdf documents into the SEARCH_WEB table. We do this via a custom application. I can see the new rows in the database.

The documents are added to DR$SEARCH_WEB_INDEX$I as I can see the tokens in this table for the new documents.

Add further different documents to the SEARCH_WEB table.
The SQL that I am calling now to repeat the problem is shown below. This used to run as a database job, but for now I am just manually running it myself. Please understand that this used to work really well, but has stopped working all together now, so I doubt that you will be able to repeat this issue in your test environment.

exec ctx_ddl.sync_index('search_web_index');

When I call exec ctx_ddl.sync_index('search_web_index') no new documents are added to the index. I can see this by looking in DR$SEARCH_WEB_INDEX$I. Please understand that this used to work.

To get around this problem instead of calling
exec ctx_ddl.sync_index('search_web_index')
I called:

drop index search_web_index;

create index search_web_index on search_web(search_content) indextype is ctxsys.context parameters('STOPLIST doco_stoplist filter ctxsys.inso_filter');

The new tokens would then appear in DR$SEARCH_WEB_INDEX$I.

so finally instead of dropping and recreating, why documents are not getting indexed while syncing.

please help me as early as possible.

Thanks
Rajesh