i have an requirement that : i want to search all the columns of all the tables in the particular database based on the specific key word or an free text.
table 1: columns data
table 2 columns data
table 3 columns data
organization name sam
organization id 1
when i search for text " SAM"
it should search me from the entire database, all tables and columns of it and display the result
output : tablename cloumn value
table1 empname sam
table2 deptname sam
table3 organizationame sam
the example is just an sample not the real data .
please help me with sample code or any link related to it .
thanks in advance
thank you very much for providing me the link.
but the problem is when i use that procdure that is given out there in that link it is very time consuming.
like it is taking approximatley 0.75 sec for database size of just 20 mb.
so what will be the case where i am using the database of around 4gb to 8gb
i think it will kill the system by taking so much time .
so is there are alterantive way which could be help full to me.
thankx in advance
As Tom indicated, this won't be quick. Tom noted a couple of different optimizations that would make it less inefficient, such as looking for all the columns in a particular table at once rather than doing a table scan for each column.
From a business perspective, what is the problem you're trying to sovle? It seems highly unusual that you would want to scan all character columns in all tables for a particular string with any sort of frequency. Why is it that you are searching for a string but you don't know where the string exists?
Hi justin , thanx for the reply
the basic requirement that we required is ,
the user will just type the keyword( value in the coumn) he required and it should search all the tables and columns of the table in the database and i have to show this in the front ent in the table format. here the user will analyse the information based on the search .
it is just like the google search we does( type the keyword in free text) it will display the result.
so for that i have to search entire table and columns in the whole database.
please if any one provides me the solution it will be help full for me.
thanx in advance
But again, why do you not know what table/ column the user is trying to search through?
In the Google example, there is a single (logical) index of web pages that needs to be searched. Why do you have different tables with different columns that are all trying to get searched together?
Thanks a lot justin,
but here it is mandatory for me to search all the table and columns. here different users will update data in different tables and also uploads the document in the tables. so in order to analyze the data i have to go through all the tables and columns .
is there any other solutions. it is just like we are investigating and analyzing some thing based on the input string and output it display( just like linking from one record to another record and analyzing and making decisions etc).
Thanx in advance
No easy way out on this one, I'm afraid. Oracle Text simply isn't designed to do this.
If you want to use Oracle Text, you will simply have to figure out which tables and columns the search term could appear in.
Then you'll either have to create lots of text indexes on each table involved (probably using the multicolumn datastore) and search each individually, or you'll have to figure out some sort of huge view across all the tables, and use the user datastore to create a single index which spans all these columns (you'd have to create a "shadow" table with one row per row of the view in order to have something to create the index on).
If you want to search across multiple columns within just one table, then, as previously stated, you can use Oracle Text with a multi_coumn_datastore. There are plenty of examples in the online documentation and this forum. In the future, please start a new thread for your own question, instead of hijacking an old thread. You can provide a link to a related old thread.