I have database (Oracle) with many tables, for example: CLIENTS, PRODUCTS, OPERATIONS etc. I want to implement searcher, which will allow me to write a phrase 'MA' and give a hints:
MA RIA Client
MA STERMIND Product
RE MA PPING Operation
etc...
How to achieve that? What is the approach to do that? Should I create another table will all names (client name, product name, operation name...)? Or just searching in all table, using many LIKE conditions? How to do this efficiently?
Many people marked my question as duplicate, so I want to explain: I am not looking for solution which I want to use one time, I need to implement it one production environment, where many people will be searching information like that above. I know I could create dynamic sql from dba_tables, dba_tab_columns views, but I want to prepare efficient way to do that! Using LIKE in WHERE clause will not use indexes, so I will have full table scans - that is not gonna work in system with many users and big tables. For know I would consider creating table with keyword with domain index on keyword column and using CONTAINS instead of LIKE, but maybe you have another idea. Maybe another database engine have features which could help me?
