Re: Oracle Text
- From: Bill Ferguson <wbfergus@xxxxxxxxx>
- To: michaeljmoore@xxxxxxxxx
- Date: Mon, 21 Sep 2009 08:37:08 -0600
Hi Michael,
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
