How to enable FND_HELP search by creating an index on FND_LOBS efficiently

Have you ever tried to use the search option of Online Help in Oracle Applications?
Our users did... and they got "The page cannot be found" message...
I checked it on firefox, hope to get more accurate message, and I got this:
"Not Found. The requested URL /pls/DEV/fnd_help.search was not found on this server".
  I checked fnd_help package and it's compiled and looks fine.
These errors appeared in error_log_pls:
[Mon Jul 28 10:34:54 2008] [warn] mod_plsql: Stale Connection due to Oracle error 20000
[Mon Jul 28 10:34:54 2008] [error] mod_plsql: /pls/DEV/fnd_help.search ORA-20000
ORA-20000: Oracle Text error:
DRG-10599: column is not indexed
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1209
ORA-06512: at "SYS.DBMS_SQL", line 328
ORA-06512: at "APPS.FND_HELP", line 1043
ORA-06512: at "APPS.FND_HELP", line 873
ORA-06512: at line 20
I found note 306239.1 - "Cannot Search Online Help After Fresh Install of 11.5.10" which suggest that Applications interMedia Text indexes are corrupt. In my instance it even wasn't exist...
I followed this note which instructs to:

1. drop index FND_LOBS_CTX;
2. Rebuild the index using aflobbld.sql

But aflobbld.sql had been running for more than 10 hours and the size of DR$FND_LOBS_CTX$I table has reached to 35 GB !
I had been wondering how it can be that fnd_lobs table is less than 1GB and the index on it is 35 GB and counting.... ?!
Note 396803.1 - "FND_LOBS_CTX is having huge size, how to reduce the sizeof the index?" suggests it's a bug, indexing all documents in FND_LOB table, also the binary files, while using wrong filter.
So how can we make aflobbld.sql to index only FND_HELP documents?
For each row in FND_LOBS table the file_format column is populated with one of the following values: IGNORE, BINARY, TEXT.
aflobbld.sql will index only rows that have this column set to BINARY or TEXT.
If we set all rows to IGNORE except FND_HELP rows, we could index them only.
Note 397757.1 - "How to Speed Up Index Creation on FND_LOBS by indexing Only FND_HELP Data" suggests the steps to do it.
These are the steps:

1. Backup the fnd_lobs table before updating it, we will use it later:
    
   create table fnd_lobs_bk as select * from fnd_lobs;  
   ** you can create a backup of this table and omit the file_date column to make this backup faster
    
2. Drop index FND_LOBS_CTX if exists:
    
   drop index applsys.FND_LOBS_CTX;  
3. Update all rows to IGNORE except FND_HELP rows:
    
   update fnd_lobs
   set file_format = 'IGNORE'
   where nvl(program_name,'@') <> 'FND_HELP' ;  
4. Execute aflobbld.sql from OS terminal:
    
   sqlplus apps/sppas @$FND_TOP/sql/aflobbld.sql applsys apps;  
5. Since I'm not sure about the impact of leaving the FND_LOBS rows as IGNORE, I updated them back to the previous state:
    
   create unique index fnd_lobs_bk_u1 on fnd_lobs_bk (file_id);
    
   update (select fl.file_format ffo,flb.file_format ffb
           from fnd_lobs fl
               ,fnd_lobs_bk flb
           where fl.file_id = flb.file_id)
   set ffo=ffb;
    
   drop table fnd_lobs_bk;  
6. Check the search option.... it should work now.

You are welcome to leave a comment .