| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Opinions on Indexing options for search capabilities
Bill,
I have implemented something similar for a customer several years ago. The idea was that there were some chemical products that had to be retrieved from searches on many tables, all more or less related to the base table that had a "product_id". The trick was therefore to use indeed the indexation not of base tables, but of the output of PL/SQL procedures, as long as we could relate the information to a "product_id". Fairly easy with all tables that had a FK to the product table (since the product_id was in the table, all you had to do was indeed to glue all columns together), but for tables that were referenced by the product table I had to index what was in fact the result of a join. In one case the reference was even indirect.
I am afraid that I have a very fuzzy recollection of the details, but all I can tell you it that it works, but you have to index each possible join in turn, which can possibly mean in your c ase 20 or 25 different indexations at least (in my case fewer than 10 tables were involved).
HTH
Stéphane Faroult
On Sat Sep 9 16:48 , William B Ferguson sent:
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.org09/08/2006 06:55 AM
wbfergus@usgs.gov
"oracle-l" , oracle-l-bounce@freelists.org 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 ~
 |
 |