Re: Opinions on Indexing options for search capabilities

Well I'd certainly query the 'business requirement'. It sounds rather like 'I know what I want to search for, but not where it is - you know like the windows thingy that lets me find stuff in files when I've forgotten all about it'. Which is to say not very db friendly.

On the other hand at my last place of employment we managed to specify something like this, together with required response times of <5s. When I saw this I was amazed, but not nearly so amazed as I was at the software suppliers response. They said 'sure no problem'

Here is what they did.

First they created a pseudo audit table this

TABLE_NAME FTEXT FTEXT_ID PRIMARY KEY)
CONTENT CLOB. I think there were likely dates and status fields as well.

then a post insert/update trigger was placed on each table that updated the 'audit' table with the new content in the following format

'~<table_name>~<pkey value>~<column_name>~<content>' for each textual column in the base table. Then an OracleText index was created on this audit table and it was that which got queried and the user got to see the content and a (user friendly) representation of the data item in question by parsing the clob content for the records returned. .

It was probably one of the ugliest hacks that I have ever seen, but in a never deleted database with low update frequency and not very well educated users it worked suprisingly well. It will clearly not scale, nor will it handle deletes, but I offer it as an ugly hack for your delectation and delight.

On 9/9/06, William B Ferguson <wbfergus_at_usgs.gov> wrote:
>
>
> Anybody have any ideas, opinions, or test case examples?
>
> I'm a complete context newbie and really have no idea if it's a viable
> option or not, or if it would simplify my requirements.
>
> Thanks.
>
> -----------------------------------------------------------------------------
>
> Bill Ferguson
> U.S. Geological Survey - Minerals Information Team
> PO Box 25046, MS-750
> Denver Federal Center
> Denver, Colorado 80225
> Voice (303)236-8747 ext. 321 Fax (303)236-4208
> ~ Think on a grand scale, start to implement on a small scale ~
>
>
> *William B Ferguson <wbfergus_at_usgs.gov>*
> Sent by: oracle-l-bounce_at_freelists.org
>
> 09/08/2006 06:55 AM Please respond to
> wbfergus_at_usgs.gov
>
> To
> "oracle-l" <oracle-l_at_freelists.org>, oracle-l-bounce_at_freelists.org cc
>
> Subject
> Indexing opinions for search capabilities
>
>
>
>
>
>
>
> Hi all,
>
> I have about 30 data tables with a total of around 250 fields. I also have
> the requirement that the users should be able to search all fields for any
> kind of value.
>
> So, I'm currently debating which would be the most effective way to
> implement this. At first I was debating between either a big index for each
> table with all of the columns vs. a seperate index for each column with the
> primary key.
>
> Then I was reading on asktom the other day (
> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:440419921146)
> about context, so now I'm debating using that approach, but I know nothing
> about it other than what I've read.
>
> My structure is such that most tables are strictly a parent-child
> relationship, so those won't be too difficult to combine into a large
> 'super-query' for context, but I do have 4 tables that reside in a
> parent-child-grandchild relationship as well. I have no CLOB's or BLOB's at
> this time, though they will be added in the future. I'll also have to add a
> few sdo_geometry fields in the future, but I don't think those really
> pertain to this issue.
>
> I'm thinking that using the context approach would be far simpler to
> implement, as I can then simplify my search 'form' to a single textarea
> field, instead of a huge form with about 250 search fields, and then the
> programming and logic for multiple criteria per field, etc.
>
> Does anybody have any experience with the two approaches and hopefully
> some pertinent examples of how context IS the preferred (and most efficient)
> solution?
>
> My parent table has about 330,000 rows, and my largest child table only
> has 750,000 rows, if table rowcounts make any difference.
>
> Thanks.
>
> -----------------------------------------------------------------------------
>
> Bill Ferguson
> U.S. Geological Survey - Minerals Information Team
> PO Box 25046, MS-750
> Denver Federal Center
> Denver, Colorado 80225
> Voice (303)236-8747 ext. 321 Fax (303)236-4208
> ~ Think on a grand scale, start to implement on a small scale ~
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l