Re: Need oracular advice on indexes.

Bruce Dodds wrote:
>
> 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....
> Bruce Dodds

If the other applications using the index don't care, you can change the column order of the existing index so your two columns are first two columns in the index. Then your selects will be able to use the index.

Even then, you want to do explain plans on your select statement. If your optimizer goal = choose and there are statistics on the table, the optimizer might choose to do full table scans instead of using the index.

Adding a new index is painful to inserts, updates and deletes, so if possible, try not to add new indexes to it.

brucer Received on Thu Nov 07 1996 - 00:00:00 CET