Re: SELECT puzzle

In article <765671012snx_at_kbigate.stgt.sub.org>, willyk_at_kbigate.stgt.sub.org (Willy Klotz) writes:
> 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 */

If you're retreiving the whole table, as you are here, it is actually quicker to do a full table scan than to use the index, so drop the where a1 > 0.

>
> 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)
> group by b1
> order by b1

again, drop the b1 > 0 and b2 > 0, it's not doing you any favours.

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

Same again, drop the use of the index, it's not helping.

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

-- 
--------------------------------------------------------------------------------
Steven Champness                   champs_at_cnb07v.hhcs.gov.au
Dept of Human Services & Health    Brisbane, Queensland, Australia
The opinions expressed above were found in a box of 'Rice Crispies'