Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Index on partial field? No - so then?

Re: Index on partial field? No - so then?

From: Sylvie Bérubé <sberube_at_cam.org>
Date: 1997/02/04
Message-ID: <32F7CBBB.7EB6@cam.org>#1/1

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US