Found a nice post on rittman.net in reference to a simple search solution posted on Eric Mortensen’s blog.

It allows for a simple interface and a leverages the basic Oracle SQL wildcard (%) to implement a search on a full table basis. It’s a nice straightforward solution that takes all fields and concats them into one field that is delimited. Using this format, one can quickly right simple SQL that does simple searches based on a simple field replacement.

I wonder if this couldn’t be extended to use regular expressions, and if so which would provide better performance. I’m certain that oracle is tuned for text scanning on %, so I’m not sure that regular expressions would beat out the wildcard searching.

Perhaps this could even be implemented as a view, but I’m sure there might be trade offs by actually having to access the records in to the other table. Perhaps a fast refresh materialized view or an actual materialized view could solve that problem.