Re: optimizing join query statement

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)