SELECT puzzle

Today I got a SQL-problem I was not able to resolve. I got it working, but performance was very poor. So I thought I will ask netland... :-)

There are three tables, lets call them A, B and C.

Table A has columns A1, A2 and A3; there is a unique index on A1.

Table B has columns B1, B2 and B3; unique index is (B1, B2).

Table C has columns C1, C2 and C3; unique index is (C1, C2).

(actually, fields A1, B1 and C1 represents the same item, eg part-number. B1 and C1 can only occur if A1 exists; A1 does not necessarily have B1 or C1).

Actually, all tables have more columns; but I tried to keep it simple.

Table A has 200.000 rows; Table B and C have 3 million rows each.

What I want to get is

    sum(B3) + sum(C3)

for every record of A1 in table A. Records in B which occur in C should not count; this means, I have to use sub-select.

Because of the large amount of data, my goal is - to have minimal calls to the database
- to use minimal temporary space.

My current solution is to open three different cursors, and then merge the resulting records in a PL/SQL Script. The cursors look something like

declare cursor a1_cursor as

   select a1, a2, a3 from A

      where a1 > 0 /* will use index */

declare cursor b1_cursor as

   select b1, sum(b3), count(*) from b

      where b1 > 0 and b2 > 0    /* will use index */
      and (b1,b5) not in
         (select c1,c5 from c 
              where c1 = b1 and c5 = b5)

declare cursor c1_cursor as

   select c1, sum(c3), count(*) from c

      where c1 > 0 and c2 > 0 /* will use index */    group by c1
   order by c1

This way, I think I can minimize the interaction between the RDBMS and the application.

Any ideas how to optimize this problem ? I tried using a view, and using SELECT... UNION for B and C; but they both try to first resolve the complete query and therefore need a huge temporary space ?

BTW, I am using Oracle V6 on a RS/6000.

Any comments are welcome.

Willy Klotz

Willys Mail     FidoNet   2:2474/117  2:2474/118     Mail Only System
                CIS: 100020,3517       USR Courier HST dual standard
                willyk_at_kbigate.stgt.sub.org


                ->   No Request from 06.00 to 08.00 <-


======================================================================