A developer in my company has recently begun lamenting that Oracle can't search more than one text field at a time. He has also mentioned in a mild, ribbing way, that mySQL has a generic function, built into the database, that allows a user to search for a fragment of text across all fields of a table without needing to specify the specific columns.
As a programmer I can think of many ways to "code" this (e.g. extra field where all text from varchar fields is concatenated as a After Update trigger) but none that don't overly complicate every table I create. I have heard that the mySQL version isn't indexed but is reasonably efficient. This feature intruiges me as many of the searching interfaces into the database aren't coded by my group and any built-in features would implicitly be available to the end users could help them (and lower my support costs).
If you had to write such functionality how might you start? Or, hopefully, is this functionallity already available and I just need to do more research. I am a long-time reader of this site and I know you are probably going to say "You should talk to the users and find out specifically what columns and what tables they want to search, build indexes, and call it a day". I can't, unfortunately, as they don't know what table or even what fields the data resides in that they are searching for and this "generic" search across a table would be very useful for them, and the client is (nearly) always right.
Thanks for any ideas you might throw my way in advance.
There are quite a few ways to do this.
The first that jumps to mind is a ctxcat index:
</code>
http://docs.oracle.com/cd/B10501_01/text.920/a96517/ind.htm#1011701
another is a text index on a function you write (this is how asktom works) that actually indexes any data you want to assemble. See
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:440419921146 <code>
for a simple example. Here on asktom I store the QUESTION you asked in one table. I store the ANSWER I gave in another table. I store the followups you supply in yet a third table (many rows -- one row for each of the followups you guys put and one row for my followup to your followup).
Now, when you search, I basically say:
select id from question_table where contains( indexed_text, 'your search') > 0
there you go -- I just searched 3 tables -- ACROSS many rows -- treating them as if they were one row in some virtual table somewhere. Hmm, does mysql do that?
Also -- put 1,000,000 rows in mysql with 1000 columns. Now -- using any technique mysql has, any at all -- perform a wildcard search on that data and return to me the answer in under 0.5 seconds. Now, do that in Oracle with either of the above options (and you can). Scale that 1 million up and up and up and see what happens. Hmmm
So, I would probably use intermedia here -- glue the fields together -- index that and let them use keyword, fuzzy, soundex, near, ands, ors, etc etc etc etc etc...
or -- it would be trivial to code a stored procedure that returns a ref cursor that dynamically built a where clause that where'ed on each field based on the columns it discovered in the data dictionary. should take about 3 minutes to code that one (and hey -- it did!)
scott@ORA920.US.ORACLE.COM> create or replace procedure query_table( p_tname in varchar2,
2 p_string in varchar2,
3 p_result_set out sys_refcursor )
4 authid current_user
5 as
6 l_query long := 'select * from ' || p_tname || ' where 1=0 ';
7 begin
8 dbms_application_info.set_client_info( '%' || p_string || '%' );
9 for x in ( select column_name from user_tab_columns where table_name = upper(p_tname) )
10 loop
11 l_query := l_query || ' or ' || x.column_name || ' like sys_context(''userenv'',''client_info'') ';
12 end loop;
13 open p_result_set for l_query;
14 end;
15 /
Procedure created.
scott@ORA920.US.ORACLE.COM>
scott@ORA920.US.ORACLE.COM> variable x refcursor
scott@ORA920.US.ORACLE.COM> set autoprint on
scott@ORA920.US.ORACLE.COM> exec query_table( 'emp', 'CLERK', :x );
PL/SQL procedure successfully completed.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
scott@ORA920.US.ORACLE.COM> exec query_table( 'dept', 'BOSTON', :x );
PL/SQL procedure successfully completed.
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
but that would be about as efficient as mysql could get it -- no indexes, full scan -- brute force every field.