FILE_FORMAT count mismatch in FND_LOBS

"Rebuild Help Search Index" Concurrent program was running for long time. So in order to troubleshoot followed the note 397757.1 (How to Speed Up Index Creation on FND_LOBS by indexing Only FND_HELP Data).

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

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

select

FILE_FORMAT,

count(*)

from

applsys.fnd_lobs

group by FILE_FORMAT;

FILE_FORMA   COUNT(*)

---------- ----------

TEXT            33378

IGNORE           6401

BINARY           7108

binary          91393

text            15754

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

2. Set the file_format column of all rows other than FND_HELP so that they won't get indexed:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

update applsys.fnd_lobs

set FILE_FORMAT = 'IGNORE'

where NVL(PROGRAM_NAME,'NULL') != 'FND_HELP' ;