I have a Table where each record is made up of a VARCHAR2 and a CLOB. The VARCHAR2 is the primary key. There are currently 1.5 million records and growing. The table is only ever used for REGEX searches on the CLOB field. The primary key is just used to prevent duplicate insertions. To speed up searching, I'd like to build a secondary index of regular words found in the CLOB field.
I was thinking that maybe another table would act as an index. The primary keys of this new table are every unique word found in the CLOB field, of every record, of the original table. The other field would be a list of pointers to the records where that word exists in the original table.
Is there a more conventional way to do this? The original table will grow to over 10 million records. How would I build a secondary index that significantly speeds up REGEX searches by first searching for any regular words found in the expression, then, after taking that smaller list of returned records, it would search using the real REGEX search?