Dear Members,

We have a requirement where user wants to perform a search on attachments stored in PC_LINK_ATTACHMENT table. As you can imagine , they would like to search on file name , file description , category . They are stored in specific column on link attachment table. As user cannot type in exact file name or description, we have to use "contains" (I know it will degrade query performance). Data Page is sourced from Report Definition to run PC_LINK_ATTACHMENT.

There are close to 5.5 Million records in PC_LINK_ATTACHMENT and our query is getting timed out which is very much expected. There are indexes available in some columns of this OOTB table too. 

Can I request for some expert opinion on how to design it in more efficient manner. We are thinking of designing full text search / elastic search but couldn't able to find more information on how to implement it.

Custom Search Property which enable us to create dedicated index and text search capability only allows to create it in Rule- or Data- or Work- class.

Regards,

Nikhil