Re: Need oracular advice on indexes.

In <3275488C.61BD_at_juniper.com>, Bruce Dodds <bruce_at_juniper.com> writes:
>I'm writing the client side of a C/S application that connects to Oracle
>7.1 tables. One of the Oracle tables is a dog, and not for my
>application alone.
>
>This table has 200,000 rows and a 104 byte, six field primary key. It
>holds active and inactive records. A third of the records are active,
>but they are responsible for 95% of the system activity, and all of my
>application's activity. The table is quite volatile.
>
>The first and sixth of the fields in the primary key, totalling nine
>bytes, could be used to uniquely identify active records. This first
>field is the field used for almost all of the joins against the table.
>
>Would we be better off adding an index based on these two fields? If
>so, how much better off?
>
>I'm not an Oracle expert, so I would like some advice before making
>suggestions.
>
>Thanks,
>
>Bruce Dodds

It wouldn't hurt to create an index on these two columns. Afterward do an EXPLAIN PLAN to see if the index was used on the query. If it wasn't you could use a HINT to force it to use the index. Remember that you must analyze the table if you are going to use cost-based optimization.

D. Scott Moyer, Jr.                        dmoyer_at_gpu.com (preferred for work)
GPU Service Corporation                dsmoyer_at_enter.net (preferred for home)