Re: optimizing join query statement

Brian W. Gardner wrote:
>
> Binh H. Tran (binh_at_cod.nosc.mil) wrote:
> : 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.
> Well, the problem here is that you are doing a count(distinct) which
> inherently does a GROUP BY. The grouping is done first. I don't know
> of a way out.
>
> --
> Brian W. Gardner "Captain, I protest; I am not a
> 495 Park Blvd. merry man!" - Lt. Worf
> Worthington, OH 43085
> bgardner_at_infinet.com (home) bgardner_at_hublink.com (office)

Try SELECT ....

   FROM C, B, A
   .....
Which will make C as your primary table instead of table A which is a large table.

Also try the query with EXPLAIN PLAN and see which indices are bring used. Also make sure that you are using COST BASED OPTIMIZER instead of the rule based optimizer.

(My $0.02...)

Good Luck,

Ashok Kapur
akapur_at_thomtech.com Received on Thu Nov 16 1995 - 00:00:00 CET