Delivery-Date: Tue, 27 Jul 93 05:17:41 PDT
From: Bart Maessen <Bart.Maessen@fel.tno.nl>
Message-Id: <199307271224.AA26180@fel.tno.nl>
Subject: re: re: joining two big tables
To: aoki@postgres.berkeley.edu
Date: Tue, 27 Jul 93 14:24:18 MET DST
Cc: bart.maessen@tnofel.fel.tno.nl, oosterom@tnofel.fel.tno.nl

Dear Paul,
 
I already got a question from Avi Pfeffer asking
more specific information. I have sent my answer to him
directly (perhaps I should not have done this). However,
I did not ftp my test data until now. Like you said, I have
put them on toe.cs.berkeley.edu:pub/upload. The files are
called table1.copy, table2.copy, table_join.copy and table_join2.copy.
I have included the message from Avi and my answer to him 
in this mail. My answer includes a c-shell script which
show things are used and tested. Please change the path names
to the apprioriate ones in the script.
 
Good luck, with it.
 
kind regards,
 
Bart Maessen
Physics and Electronics Laboratory TNO
The Hague, The Netherlands.
e-mail: bart.maessen@fel.tno.nl
 
 
Avi's message:
>Could you describe the queries you use?
>
>avi
end of message
 
My answer to Avi:
>Hi Avi,
>
>The database schema's and queries I use are basically simple.
>I have used several shell scripts for this problem.
>In this mail I include the most simple script.
>The files mentioned in this script: table1.copy, table2.copy and the
>(different) results of two attempted joins I can send you.
>They are about 4MB, so if you have any suggestions where I can ftp these
>to you,  you can have a look at them also.
>
>Good luck with it,
>
>Bart Maessen
>Physics and Electronics Laboratory TNO,
>The Hague, The Netherlands.
>e-mail: bart.maessen@fel.tno.nl
>
>
>------------------------------------------- cut here -------------------------
>#! /bin/sh
>
>DB=${1:-digest}
>
>set -v
>monitor -c "create table1(id=int4,some_int=int4,some_text=text)" $DB
>
>monitor -c "create table2(id=int4,some_int=int4,pointer_to_a=int4,some_text=tex
t
>,another_text=text)" $DB
>
>monitor -c "copy table1 from \"/fusr/bmaa5/test_target4_tdn/table1.copy\"" $DB
>
>monitor -c "copy table2 from \"/fusr/bmaa5/test_target4_tdn/table2.copy\"" $DB
>
>monitor -c "retrieve into table_join (
>id=a.id,
>some_int=a.some_int,
>some_text=a.some_text,
>some_text2=b.some_text,
>another_text=b.another_text)
>from a in table1, b in table2
>where
>a.id = b.pointer_to_a and
>a.some_int = b.some_int" $DB
>
>monitor -c "copy table_join to \"/fusr/bmaa5/test_target4_tdn/table_join.copy\"
"
> $DB
end of answer to avi