Re: Oracle Query Tuning

If you join more than 2 tables in a query the order of the tables in the FROM is important. Oracle will join the first 2 tables, generate a result set, then join this to the 3rd table. You should order the tables so that the first 2 return the smallest result set, the join between this result set and the 3rd table will then have fewer records to evaluate. As far as your indexing, an index on a field that can only contain a 'Y' or a 'N' will probably not help you much. I believe Oracle has what is called a binary index for this type of record - you might want to look into this.

In article <69usl1$ms7$3_at_gte2.gte.net>,
  "Pei L. Ku" <pku_at_gte.net> wrote:
>
> I'd recommend to you to familiarized yourself with tkprof and explain plan.
> These are valuable tools in determining resource consumption and execution
> plan of your SQLs. If you are running on SMP machines, look into Parallel
> Query features.
>
> Pei
>
> 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
> >

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet