Re: Oracle Query Tuning

Hi Manoj,

By having index on all the fields of a table and by running the query again the index would not always result in the best performance. Its a wrong notion. The cost of using a Index is over weighted at time by full scan. Just have this in mind.

From u posting its not certain about ur Server version.

Under 7.3 u have Histograms. Historgarms enable u to tune queries against tables whose data values are not uniformly distributed.

Under 7.3 u have bit map indexes. Which seem to be most approriate for ur case from the posting. Ur Y and N colunms should be cadidate for these. Bitmap indexes are appropriate when multiple onselective columns are used as limiting conditions in a single query. But note that the more bitmap indexes there are on a table, teh greater the cost will be during each transaction. To put it in form of a thumb rule, it the more columns used as a limiting conditions, the more valuable bitmap index will be.

Also check the explain plan or trace to view how the optimizer plans to run ur query, That might give more insite. Plus analyze the tables that also changes the plan in cost based optimizer.

HTH Sameer Mahajan
sameermj_at_usa.net

manoj.lahoti_at_gepex.ge.com wrote in article <885079291.2007040019_at_dejanews.com>...
> 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.
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
>
Received on Sun Jan 18 1998 - 00:00:00 CST