Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 10843 invoked from network); 9 Sep 2006 09:49:11 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 9 Sep 2006 09:49:10 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E48BB404EA9; Sat, 9 Sep 2006 10:48:44 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 09510-04; Sat, 9 Sep 2006 10:48:44 -0400 (EDT) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5D9A0409DD1; Sat, 9 Sep 2006 10:48:44 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Sat, 09 Sep 2006 10:47:46 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7E68240C15D; Sat, 9 Sep 2006 10:47:46 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 08870-09; Sat, 9 Sep 2006 10:47:46 -0400 (EDT) Received: from gscodenm05.cr.usgs.gov (gscodenm05.cr.usgs.gov [136.177.7.15]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 266EA40C08A; Sat, 9 Sep 2006 10:47:46 -0400 (EDT) In-Reply-To: To: wbfergus@usgs.gov Cc: "oracle-l" , oracle-l-bounce@freelists.org Subject: Re: Opinions on Indexing options for search capabilities MIME-Version: 1.0 Message-ID: From: William B Ferguson Date: Sat, 9 Sep 2006 08:48:07 -0600 X-MIMETrack: Serialize by Router on gscodenm05/SERVER/USGS/DOI(Release 6.5.5HF94 | February 6, 2006) at 09/09/2006 08:48:11, Serialize complete at 09/09/2006 08:48:11 Content-Type: multipart/alternative; boundary="=_alternative 00519BFD872571E4_=" X-archive-position: 39208 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: wbfergus@usgs.gov Precedence: normal Reply-to: wbfergus@usgs.gov X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at avenirtech.net --=_alternative 00519BFD872571E4_= Content-Type: text/plain; charset="US-ASCII" 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 Sent by: oracle-l-bounce@freelists.org 09/08/2006 06:55 AM Please respond to wbfergus@usgs.gov To "oracle-l" , oracle-l-bounce@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 ~ --=_alternative 00519BFD872571E4_= Content-Type: text/html; charset="US-ASCII"
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@usgs.gov>
Sent by: oracle-l-bounce@freelists.org

09/08/2006 06:55 AM
Please respond to
wbfergus@usgs.gov
To
"oracle-l" <oracle-l@freelists.org>, oracle-l-bounce@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 ~
--=_alternative 00519BFD872571E4_=-- -- http://www.freelists.org/webpage/oracle-l