Re: Oracle Query Tuning

On 16-Feb-98 05:42:11 manoj.lahoti wrote:

>I'm trying run a select query against 3 oracle tables with 250,000 ,45000
>and 5000 records respectively. I have indexes on almost all the fields
>that i'm using in where clause of the query. Now this query took about 30
>min to fetch the results when I tried to create index on a field which
>has only 2 values i.e. 'Y' and 'N, otherwise it takes about 10 -15 min to
>run this query. Any suggestions to improve the query response would be
>appreciated.

Over-indexing a table often does more harn than good. Especially if you query a table that has two or more quite unselective indexes and the optimizer uses an execution plan which includes all indexes. Try using the 'explain plan' command to find out the execution plan. If it contains something like 'AND-EQUAL' you should consider dropping all but one index used in your query.
An alternative worth trying is to use bitmap indexes on unselective columns. These can increase the performance when used in conjunction with 'and' in where clauses. But they also have the disadvantage that they could interfere with row locking.

Hope that helps,
Lothar

--
Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Schulstr. 12            | lothar.armbruester_at_t-online.de
D-65375 Oestrich-Winkel |