Re: Oracle Query Tuning

On 18-Jan-98 00:41:13 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 can do more harm than benefit especially when using unselective indexes. Example:

Suppose you have three unselective indexes and a where clause using every off the three indexed columns. Suppose also that each part would result in say 40% of the whole data.
If now the optimizer choses for some reason the AND_EQUAL-exceution path, it produces intermediate results for every index and merges them. So, actually 120% of the rows are examined. Obviously a full table scan will be faster in this case.

The solution for your problem might be to drop all indexes which are used just in conjunction with others in an AND_EQUAL-Plan. If you need indexes on unselective columns with few values you should consider usin bitmap indexes. These can give great performances when used in where clauses with conditions connected via 'AND'.

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 |