Re: Optimize SELECE SQL Query

On Apr 24, 8:39 am, beerora <beersa.bee..._at_gmail.com> wrote:
> Hi,
>
> Oracle client Version 9, OS WinXP, About 6 million rows, index on
> a.name.
>
> I have a following SQL query.
>
> Select a.id,a.name,a.age,a.class,a.tel from table01 a, table02 b
> where a.name=USER and (a.id=b.id01 or a.id=b.id02 or a.id=b.id03 or
> a.id=b.id04 or a.id=b.id05 or. a.id=b.id06)
>
> The above query took long time.
>
> How can I optimize this for better result?
>
> Thanks.

Where is the explain plan?

What kind of table is table02 where the key of table01 can be equal to any of six columns in a table02 row?

I suspect that you table design is not properly normalized. If table02 is a derived table then you might be better off to go back to the source to get the table02 data.

As written I would think you should have a single column index on all six columns of table02 otherwise Oracle will have to full scan table02 up to six times in the case of the match being in id06 or when no match exists in table02.

HTH -- Mark D Powell -- Received on Wed Apr 25 2007 - 11:44:49 CDT