Re: Opinions on Indexing options for search capabilities

  • From: "Gints Plivna" <gints.plivna@xxxxxxxxx>
  • To: wbfergus@xxxxxxxx
  • Date: Mon, 11 Sep 2006 11:51:32 +0300

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@xxxxxxxx>:


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@xxxxxxxx>*
Sent by: oracle-l-bounce@xxxxxxxxxxxxx

09/08/2006 06:55 AM   Please respond to
wbfergus@xxxxxxxx

   To
"oracle-l" <oracle-l@xxxxxxxxxxxxx>, oracle-l-bounce@xxxxxxxxxxxxx  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 ~


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 09-2006