Path: utzoo!utgpu!news-server.csri.toronto.edu!mailrus!wuarchive!zaphod.mps.ohio-state.edu!swrinde!ucsd!pacbell.com!decwrl!fernwood!oracle!news
From: tgreenla@oracle.uucp (Terry Greenlaw)
Newsgroups: comp.databases
Subject: Re: Join Contest
Message-ID: <1990Jul30.190644.18874@oracle.com>
Date: 30 Jul 90 19:06:44 GMT
References: <5265@plains.UUCP> <1990Jul27.042508.10645@loft386.uucp>
Reply-To: tgreenla@oracle.UUCP (Terry Greenlaw)
Organization: Oracle Corporation (Federal Division), Atlanta, GA
Lines: 39

In article <1990Jul27.042508.10645@loft386.uucp> wes@loft386.uucp (Wes Peters) writes:
>In article <5265@plains.UUCP>, gus@plains.NoDak.edu writes:
>> Recently I heard that an IBM-type db person claimed that it's
>> not uncommon in commercial db applications to join as many as
>> 10-15 (maybe even 30) tables in a single query.
>> This seems like an incredible number of joins, especially if the
>> tables are large, on the order of 1/2 million tuples.
>> Any comment/references, etc.?
>
>My company has an application for tracking software problems and
>changes that runs on Oracle.  Last week, I tried running a selection
>that joined 3 tables*.  On our system, a cluster of 3 VAX 8250s
>running VMS 4.7 and Oracle 5.1.22, this selection would not produce
>any output in 45 minutes.  Dropping one table from the selection
>resulted in output typically in 30-40 seconds.
>
>*Actually we were selecting from 3 public synonyms for tables "owned"
>by another Oracle user.  I'm sure our selection was pretty pessimal in
>it's order of query, but still 45 minutes?  Yuck!

An application I worked on a while back was based on collecting data from 
many different sub-offices and compiling it into one report for the main
office. The report was written using SQL*ReportWriter ver. 1.1 against a
ver. 6 database and the main query had to join 22 tables to get the primary
tracking data out! The original program was written in C and took 45 minutes
to two hours to complete. Our version in ReportWriter took between 45 seconds
and 1 minute.
I would say that well over 95% of the queries I have seen were 3 tables or
less. The Surgeon General has warned that anything exceeding this amount is
hazardous to your health, and may result in high blood pressure, temporary
loss of mental facilities, death, or the irresistable urge to deploy     
high-powered explosive devices against computing machinery ;-}



Terry O. Greenlaw             Sheathed within the Walkman, 
Staff Engineer                Wear a halo of distortion.
Oracle Corporation            Aural contraceptive,
tgreenla@oracle.oracle.com    Aborting pregnant conversation - Marillion