Re: BIG Joins
Date: 19 Jul 1994 22:10:13 -0500
Message-ID: <30i4il$3jg_at_tamsun.tamu.edu>
In article <9407181214.PN01017_at_ll.mit.edu>,
James Forgy <forgy_at_ll.mit.edu> wrote:
>In article <302ahl$j4p_at_tamsun.tamu.edu> m0v5533_at_tamsun.tamu.edu (Mahesh Vallampati) writes:
>>Wow,can u provide more details like which machine used and the number of row
>>in the seven tables u joined.Again if u had used a selection criteria on the
>>600,000 row table which u use to union with the joined table then the above
>>claim would be valid because the query optimiser would be smart enough to
>>filter out the rows in the big table. As it stands i think the above is a
>>tall claim.I would appreciate it if u could give more details on the above.
>>>-jf
>>>>I`m summarize to these newsgroups.
>>>>-Jeff Evarts
>>>>--jde_at_unify.com
>That's not a claim buddy, that's called "set timing on", mabye I will download
>this SQL from an HP9000 and post it for you.
>-jf
Hah,
First of all,I am pretty sure the timing of 7 secs reported must be the time
taken by the sys part
of
real :
user :
sys :
This timing is plausible but i have my doubts.
Let me show u an SQL script which determines the maximum value in a
column of a table.
I know that there is a max function available in SQL but this is just a
small experiment.
select col1,col2 from temp
minus
select a.col1,a.col2 from temp a,temp b
where a.col1<b.col1;
I ran this query on a table with 1000 rows and this is what i got after setting "set timing on" .
Real : 229.06 secs User : 0.00 sys : 0.01 ----------------------------------------------------------This was accomplished on an RS/6000(AIX) with no other Oracle jobs running and me as the single user.
I had thought that u were telling the real part of the "set timing on" option. So that was why i raised the question. The objective of this thread was to determine "Real time" of the set timing on. The other times have no relevance except for some analytic purposes.As a Programmer/analyst one would be certainly concerned about the "real time".
So it is ur responsibility to tell what "time" u were talking about. This is the realm of TPC Benchmarks and there is no scope of ambiguity whatsoever and these are "million $$ questions".Companies and Firms spend $$$ on TPC standards to determine which systems to buy. Hope this clear things up.
>>>I have wrote a 7-Way Join with a Union on a 600,000 row table and it only took
>>>7 seconds to run.
The above statement per se doesnt mean anything.The nature of the query and
how the query optimiser optimised this is another issue also .
Thanks and Regards
Mahesh Vallampati
M.S. In EE
Dept.of Electrical Engineering,
Texas A & M University.
Ph:(409)845-6189
\\ In the Beginning there was Codd.....
Received on Wed Jul 20 1994 - 05:10:13 CEST