Re: Oracle Text

I use it for searching, and performance is fantastic! Once the indexes are created, there really isn't much to do in the way of tuning. The query syntax is a bit different, but it wasn't to difficult to figure out.

You may have seen a similar of mine before, but I have a bunch of tables and the users want to be ab able to search any field in any table. So, after geeting really stuck trying to develop an easy to use search screen, I decided to instead create an XML-like field (in a new table), with all of the data from every table for each 'master' record tagged, XML-style.

I then created the indexes, etc., and what used to take 30 minutes or more for a search dropped down to around 2-3 minutes max (many in just a matter of seconds). My XML-like table (a table with the 'master' record id and a CLOB field), is around 12 GB. I have triggers on all of my tables, so when data is changed anywhere, it fires off a procedure to regenerate the XML data for that record. There is a small performance penalty there, as if a user re-orders the records in a chaild table, then the procedure fires on each row, but it's acceptable to the users for the increased search capability.

I can easily allow the users to search specific tables/fields for specific data, or they simply do a "keyword search", and find all records with their 'word' anywhere in any of the tables.

One of the latest IOUG magazines has the details. Any questions, let me know. The hardest part was getting the initial search table populated.

--

• Bill Ferguson -- http://www.freelists.org/webpage/oracle-l