Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Index on partial field? No - so then?
Barbara Hiles wrote:
<snip>
> Ok - so I can't do that.
> So how does everybody else handle this stuff?
> We're migrating from IMS to Oracle. We have segments indexed six ways to
> Sunday. We have IO mods that do db access and for expediency sake we are
> converting them (no way we're reengineering code - no time or money or
> staff).
Usually if you want to make an index on a partial field that means you
must go back
to the database design stage and normalize your tables if possible in
3rd normal form.
I would suggest putting each "part of your index" in different fields
and then check
if those fields should not be in different tables according to
normalization.
> What are the tradeoffs for not creating indexes?
Long full table scan. How many rows are you talking about. 2,000 no
problem; 2,000,000
it starts to be. :-)
> Do you try to figure out which indexes are used most? We can and have set up
> cursors in pl/sql to essentially create the index on the fly. I'm afraid that
> once the volumes and usage go up - this isn't going to work very well (we're
> not production yet).
> So, I'm interested in comments on that.
I don't recommend indexing on the fly at all! Indexing will mean Oracle
will read
each and everyone of your rows, sort them, then create an index just to
answer one of your requests! If you need an index that means you have
quite a number of rows which
would imply everytime: enough space for sorting, plus how would you
control the index
parameter creation as your table grows. Either they are fixed and that
means you would
have to open the code to change them and recompile, either they are
dynamic and... you ask the user to determine the INITIAL or NEXT!!!
<:-o
>
> Back to the field separation - I'm still struggling with this. Not just for
> indexing sake - but in general usage too. Several indexes need parts of key
> fields. Does anyone define a composite key and then repeat the fields
> individually? I would have to enforce this with triggers - I don't really
> like the idea much. But trying to find the next record greater than with a
> key made up of 5 separate fields gets kludgy.
>
As I stated earlier... back to the drawing boards. A relational DB
doesn't work the same as the hierarchical DB. Create different fields
will all those "index partial fields" then check if they really should
be in the same tables. Then write the SQL to answer the needs of your
application with those new defined tables... then and only then explain
all of your written SQL and determine which indexes should be created.
Tuning Oracle Application book will help you there. You may discover
that not all of your indexes in IMS are needed in Oracle. It's a long
process but it's the only way.
Wish you luck.
Sylvie Bérubé
sberube_at_cam.org
Received on Tue Feb 04 1997 - 00:00:00 CST