Re: optimizing join query statement

In article <1995Nov8.231406.13755_at_nosc.mil>, binh_at_cod.nosc.mil (Binh H. Tran) writes:
|> 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)

What number does the query return?
I suggest to eliminate count(distinct ...) if the number is large.

What indices did you created?
Use explain plan to see whether your indexes are used and how they are used.

So, I suggest to probe the following select and indices if the count is large:

select count(*) from A
  where exists (select * from C

                  where C.column2=A.column2 and C.column4=A.column4)
    and exists (select * from B
                  where B.column1=A.column1 and B.column3=A.column3);

indices on B(column1,column3) and on C(column2,column4).

Hope this query will run some faster...

|>
|> 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.

---
Alexander Lukyanov
lav_at_video.yars.free.net