Improving query speed (was Re: Building a benchmark query)

From: Long <mummm_at_echelon.ca>
Date: 1 Nov 1999 19:06:20 GMT
Message-ID: <01bf249c$2f24c420$ca73048e@NQ37A>

All,

I have a Personal Oracle 7.3 table that is 100,000 plus records. It can grow
to million plus records. It is queried using six-way (6 columns) conditions on
an NT 4.0 Workstation.

I performed a set of bench marking queries on the table with and without indices. Indices are there to improve query responses, however I am finding that queries on fully indexed table actually is slower than without.
Also, partial indices on the table seem to improve the speed of some types of queries but much poorer on other types.

Currently the indices are created as a compounded index (i.e. an index with one or more columns). If I create multiple indices with one column per, would this improve the query response time? Do I need to do anything special to tell the system to use the multiple index?

Your experience and comments are greatly appreciated. TIA.

/Long Received on Mon Nov 01 1999 - 13:06:20 CST