Hi

I have got the requirement for the database that the fields that
are search-able are not known up-front. In my mind I would need
one column for each field, and index those columns. A co-worker
has mentioned the use of some "lookup tables", but I can't see how
any helper table can solve the problem with indexing.

If anyone knows how to handle this kind of problem (both flexible
and fast), I would appreciate any response.

Morten Simonsen

On Feb 26, 5:50 am, Morten Simonsen <mor... (AT) owera (DOT) com> wrote:
Answers vary with DBMS product. Given you provide no mention of what
DBMS you use, no mention of the transaction environment (data
warehouse, transaction process, online processing, reporting) don't
expect any one answere to solve your problem.

Secondly, paraphrasing the general sugestion that you can have 2 out
of 3: Fast, Flexible, and cheap, It looks like you are about to spend
lots of money. (Are the schools no longer producing Software Engineers
that understand the word Tradeoff?)

Possible solutions:

What's wrong with indexing every column? If it is a data warehouse,
then loads are infrequent and can be done during off hours. This
assumes indexing will help your query performance. That is not a
guarantee.

Lookup tables to solve this would duplicate data but would merely take
the place of indices. The lookup table would have the child column and
the PK columns of the base table (FK'ed to the base table). The index
would then be on the lookup table. It doesn't solve indexing, it
merely moves it out of the base table. Oracle has Index Organized
Tables (IOT) in which case the helper table is an index in disquise.

For the solution that could really help, load teh table in RAM. (told
you you were going to spend money). That is the generic way to get
both fast and flexible access to the data.

Hey, you asked a generic question. It is hard to get specific.

Last suggestion: the best way to determine how to optimize DBMS
performance is to know the data loaded in it. Other than that the way
to improve performance is to test,
and tes,
and test.

Ed