Re: Opinions on Indexing options for search capabilities

  • From: William B Ferguson <wbfergus@xxxxxxxx>
  • To: "Gints Plivna" <gints.plivna@xxxxxxxxx>, "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
  • Date: Mon, 11 Sep 2006 06:56:20 -0600

Hi Gints and Niall,

Thanks for the responses. I've been doing some reading on the subject this 
weekend, and while I'm still kind of lost of the whole implementation 
part, I think I'm 'refining' the search requirements a bit more.

Part of the problem is a 'group' that says "I can do this so easily in 
Filemaker", but the Filemaker version does have the user specify in which 
field what they are looking for, so potentially around 250 fields or so on 
multiple screens. Since my version is being built in Apex, I'm beginning 
to lean a bit more towards a set of tabbed screens. My application is for 
mineral locations worldwide, and the tables can be categorized into either 
'General', 'Locational', 'Geological', 'Cross-referencing', or 
'Production/Resources', so basically a tabbed page for each category.

From here, I'm thinking that I'll wind up with some fields being search 
specific, like one field is for 'depth to top of orebody' with another 
field for unit of measure. Using a context search for this field is kind 
of silly, as there are also about 25 other fields that are similar, 'total 
area', 'depth to bottom', depth of workings', 'length of orebody'', 
'length of workings', etc. Grouping all these into a CLOB to index 
wouldn't return very accurate results.

However, there are other fields where the context approach would work, 
like bibliographic_references and comments, which also wind up having some 
overlap in content. So, while I can't completely eliminate a bunch of 
potential input fields for specifying the search criteria, I think I can 
at least trim it down and organize it better, so the user can skip the 
tabbed areas they're not interested in. Then, they'll be presented with a 
combination of data specific search fields, and a general (context) search 
box for that category.

The only other 'easy' option I can envision would be to create an HTML of 
all of the data for each 'parent' and all related children (Luckily I 
already have a print function that does this), and then store that 
information into a CLOB, then I could also utilize the ctx_doc.highlight 
or ctx_doc.markup to display the query results, but I'm not sure if I have 
the storage capacity for that. But then of course I also have to consider 
that each time information was changed, added, or deleted, then I'd have 
to regenerate the html page (or delete it) as well. I don't think 
performance-wise it's much of an issue, as I'll probably have a max of 10 
users at a time.

I think I'll have to create a couple search options for the users to test 
and then tell me which they prefer, or maybe even offer both depending on 
their needs. Geez, one thing leads to another, plus it looks like I'll 
need to install and figure out SSO as I'm going to Active Directory in a 
couple months, and I'll have two different databases running different 
versions of Oracle, but I'll need to pass authentication credentials to 
eliminate dual logons, and hopefully simplify the id/password 

Well, at least I still have about 16 more years before I can retire. Maybe 
they might even hire somebody to help me by then.  :^)

Thanks again.

                               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 ~

"Gints Plivna" <gints.plivna@xxxxxxxxx> 
09/11/2006 02:51 AM

oracle-l <oracle-l@xxxxxxxxxxxxx>, oracle-l-bounce@xxxxxxxxxxxxx
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
 if you haven't found it already.
And yea I've used this approach.
Gints Plivna

Other related posts: