Re: optimizing join query statement

binh_at_cod.nosc.mil (Binh H. Tran) wrote:
>Hi All:
>
>I need help on optimizing a join select statement.
>What I have are three tables A, B, and C with
>112,000, 11,000, and 5,00 records, respectively.
>
>Currently, the select statement we have is:
>
> select count(distinct A.rowid) from A, B, C where
> (A.column1 = B.column1 and A.column2 = C.column2 and
> A.column3 = B.column3 and A.column4 = C.column4)
>
>The problem with this is it takes way too long (>10 minutes)
>to produce the result. I created a few indices for the
>three tables, but It didn't seem to improve the performance
>at all.
>
>Any suggestions?
>
>Thanks,
>
>binh_at_nosc.mil
>

Use "explain plan" to discover the query plan that is being executed (types of access to tables etc...) and then modify the query/indexes depending on which indexes are being used/not used.

After each modification re-run explain plan on the query. Keep doing this until either the performance is what you require, or you get bored.

Of course you may be cpu, disk I/O, memory etc.. limited!

-- 
Adrian Brooks

Any views expressed are my own and not of my employer.