Indexing opinions for search capabilities

  • From: William B Ferguson <wbfergus@xxxxxxxx>
  • To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>, oracle-l-bounce@xxxxxxxxxxxxx
  • Date: Fri, 8 Sep 2006 06:55:05 -0600

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


                               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:

  • » Indexing opinions for search capabilities