single index for multiple columns
Hello all,
we have a requirement for our application, to add a single search box, that searches over 1 table for many different columns. The way we've now implemented it, is as follows (simplified example):
select *
from emp
where instr(ename,:SEARCH)>0
or instr(job,:SEARCH)>0
or ...
This aproach is relatively slow of course. I want to change that, using Oracle Text. Can you give me some direction what kind of index we have to use? Our requirement:
- index must continue be up to date (or within seconds)
- search must be fast
- preferably case insensitive
- number of columns to be indexed: aprox. 20 within one table
I once heard that you can make a function, with all the columns you want to index. that function generates an xml document and you can use that function to create an Oracle Text index. However, I can't seem to find in what way I can implement that. Or is there a better aproach?
Thanks again for your help!