| Question on indexes [message #8139] |
Tue, 29 July 2003 19:03  |
mojo
Messages: 2
Registered: April 2002
|
Junior Member |
|
|
Hello a newbie to this particular RDBMS, but can someone please help out here in regards to Oracle and indexes? We are planning a large Oracle 8i OLTP application where a common function of many screens is the user can do a search via one, or a combination of many fields, on the main table of that screen.
So for the sake of simplicity lets say I am building a massive Video Store application and I give the user the ability to search on the main Movie file. Lets say that file is comprised of the following:
MovieID (unique key)
MovieName
MainActorActress (full name for simplicity)
Producer
Director
ProducingStudio
CountryOfOrigin
Type (drop-down of Sci-Fi, Western, etc.)
Language
SubTitles
YearOfRelease
GrossInDollars
WonOscars
WonGoldenGlobe
SiskelEbertRating.
So lets say I have 100,000+ movie titles in there, but my users want complete flexibility on what they search on. In spite of trying to reason with them to give me a handful of fields to search on, they insist on being able to query on any combination of the 15 fields within the Movie file.
So what is my best approach here? My own idea is, which may be way off base, is:
- I won’t index every field as that would be much to cumbersome on the system
- present a screen with all of the fields on it, with the most likely choices near the top
- I cannot use multi-field indexes as they want complete flexibility and therefore can’t guarantee me that any specific combination of fields for a search (and I am using Oracle 8 and not 9)
- Although there are 15 fields, I will try to make a reasonable assumption that it is fair that one or more of the first 5 in the file will always be queried on and therefore will do five separate indexes on the following MovieName, MainActorActress, Producer, Director (with MovieID already having been indexed as my primary unique key)
- With all 15 fields shown on the screen I will somehow mark (highlight, special character beside the field, etc.) those five fields for the user so that they know that at least one of them must be used in any given search
– Alternatively, don’t force them, but warn them on an attempted search without any of those fields that the search may be slow
- Assuming that they choose at least one of the five fields, then hopefully the system will use the appropriate index(es) to immediately qualify those records and then manually check each ones against any other criteria (any entries in the other 10 fields).
Is that reasonable? What does everyone else do out there?
Also, how efficient is keyword searching in Oracle? Is there anything special I should know in that regard, as I can see that being a popular request in the design phase.
Thanks for any help in advance!
|
|
|
|
| Re: Question on indexes [message #8144 is a reply to message #8139] |
Wed, 30 July 2003 00:13  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Your approach is on the right track. Keyword searching (interMedia in 8i, Text in 9i) is very efficient.
If you are building the query dynamically on the front-end, be sure to use bind variables. Do NOT include the literal search text in the SQL.
If you are building the query on the backend (in PL/SQL), be sure to use application contexts to simulate the use of bind variables.
You will just kill your shared pool and consume drastically higher resources (CPU for parsing, etc.) if you cheat and don't use binds.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
