How to Speed Up Index Creation on FND_LOBS by indexing Only FND_HELP Data (Doc ID 397757.1)

The inability to rebuild the FND_LOBS intermedia index rarely merits a SEV1 as the sole purpose of this is to to provide
indexing on the online help that most customers can do without for awhile.  Problems can occur when generating this index because, as a side effect, the aflobbld.sql script tends to also index the FNDATTCH data and many customers find that wasteful.  These problems include the aflobbld.sql script (which is executed by the concurrent request "Rebuild Help Search Index") running for an extremely long time (over two hours) and the FND_LOBS_U1 and FND_LOBS_CTX taking enormous amounts of space (millions of rows).

It is possible to index JUST the FND_HELP entries.

SOLUTION:
1. Take a snapshot of the original state of the fnd_lobs entries for comfort sake:

select
FILE_FORMAT,
count(*)
from
fnd_lobs
group by FILE_FORMAT;
2. Set the file_format column of all rows other than FND_HELP so that they won't get indexed:

update fnd_lobs
set FILE_FORMAT = 'IGNORE'
where NVL(PROGRAM_NAME,'NULL') != 'FND_HELP' ;

commit;
3. Most versions of aflobbld.sql, as written, will only rebuild the FND_LOBS_CTX index if the status of the index is invalid or missing. To force the rebuilding of the index we may first have to drop it.

Check the status of the fnd_lobs_ctx index. If it is valid, then drop it.

select status
from all_indexes
where owner='APPLSYS'
and index_name='FND_LOBS_CTX'
and table_name='FND_LOBS';
If this returns a status of "valid", then "drop index applsys.fnd_lobs_ctx force"

4. Run aflobbld.sql.  $FND_TOP/sql This takes two parameters; the name of the applsys schema and the name of the apps schema. 
    Typically "@aflobbld.sql applsys apps".

5. The reasonably recent versions of fnd_gfm contain the function "set_file_format" which you can use to reset the values of the FILE_FORMAT column, based on the FND_MIME_TYPES table using the following update:

update fnd_lobs 
set file_format = fnd_gfm.set_file_format(file_content_type); 

commit;

One can verify the success of the above update by comparing the following reiteration to the previous result of step 1:

select 
FILE_FORMAT, 
count(*) 
from 
fnd_lobs 
group by FILE_FORMAT;


+++++++++++++++++++++++LOB Cleanup  on R12 instances(TS--> APPS_TS_MEDIA)
conn applsys/<pwd>
ALTER TABLE FND_LOBS MODIFY LOB (FILE_DATA) ( PCTVERSION 0 );
conn / as sysdba
ALTER TABLE APPLSYS.FND_LOBS ENABLE ROW MOVEMENT;
ALTER TABLE APPLSYS.FND_LOBS MODIFY LOB (FILE_DATA) (SHRINK SPACE);
ALTER INDEX APPLSYS.FND_LOBS_U1 REBUILD;
ALTER INDEX APPLSYS.FND_LOBS_N1 REBUILD;
$FND_TOP/sql/aflobbld.sql
SQL>   @$FND_TOP/sql/aflobbld.sql  applsys apps