Re: Queries and Subqueries

On Tue, 22 Oct 1996, Mike Ellison wrote:

> I have two tables which hold seperate data regarding accounts; the account number is the concatenation of a
> few fields (which exist in both tables). I need a query that sums up a particular field, grouped by
> account number, for each of the tables, something like:
>
> select acc1, acc2, acc3, sum(fieldA)
> from TableA
> where (criteria)
> (**returns about 3000 records**)
> and
>
> select acc1, acc2, acc3, sum(fieldB)
> from TableB
> where (other criteria)
> (**returns about 2800 records**)
>
> I've made a view for each of these queries, and now want to link them together like this:
>
> select a.acc1, a.acc2, a.acc3, a.SumFieldA, b.SumFieldB
> from ViewA a, ViewB b
> where a.acc1 = b.acc1(+)
> and a.acc2 = b.acc2(+)
> and a.acc3 = b.acc3(+)
>
> Each of the subqueries executes by themselves in just a few seconds, but the final select statement takes
> upwards on a half hour. I've tried indexing the linked fields with no success. Can anyone offer a
> suggestion as to optimizing this final view?
>
>

Doing the outer join on 3 columns could be time-consuming. Have you used 'explain plan' or 'tkprof' to verify this?

Why not try a 'union'?

ie:

 	select acc1, acc2, acc3, sum(fieldA)
           from TableA
          where (criteria)
 	(**returns about 3000 records**)
union all
 	select acc1, acc2, acc3, sum(fieldB)
           from TableB
          where (other criteria)
 	(**returns about 2800 records**)

Should be fairly quick.

Jared Still
DBA
RxNet/ValueRx
jared_at_psnw.com Received on Thu Oct 24 1996 - 00:00:00 CEST