Re: Opinions on Indexing options for search capabilities

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

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_at_gmail.com> 09/11/2006 02:51 AM

To
wbfergus_at_usgs.gov
cc
oracle-l <oracle-l_at_freelists.org>, oracle-l-bounce_at_freelists.org Subject
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  

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