Re: Opinions on Indexing options for search capabilities

I haven't understood a bit your requirements whether 1) a user can input any value e.g. "John" and your query should search it in every field (including person name, birthdate, salary, product name and disease name :) or
2) a user can choose any attribute e.g. person name and give search criterion for it - "John".

For the first task it almost looks like google :) For the second task I hope you can define one search result form with some identifying attributes helping user to understand what rows he/she found out. Here you can start from the very parent table, add up necessary joins to tables having attributes whose criteria were added and add predicates restricting search.
I think this thread for contexts is better http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1288401763279 if
you haven't found it already.
And yea I've used this approach.

Gints Plivna
http://www.gplivna.eu

2006/9/9, William B Ferguson <wbfergus_at_usgs.gov>:

>
> 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 ~
>
>

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