Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re[2]: Re[2]:Upgrading Oracle

RE: Re[2]: Re[2]:Upgrading Oracle

From: Jeffery Stevenson <>
Date: Wed, 25 Oct 2000 13:52:26 -0500
Message-Id: <>

  The physical implementation of the database design is really dependent on how the data is going to be used. I'd hate to see someone try to do an ad-hoc query on a fact table in a star schema designed database and "limit" themselves to under 4 joins. If you dump everything into a few tables, you are going to have to bite the bullet somewhere. Sure you might save a miniscule amount of time not having to join that extra table, but if you have a table with say 25 columns and you have to index 12-16 of them to handle all of the different queries going against it, your insert/update/delete time is going to pay the price. I've had databases where I've joined 12 tables (almost 4th normal form) and had it return immediately, and I've had databases where I've joined only 2 tables (heavy denormalization--2 tables with roughly 30-50 columns each) together and it took forever to complete. The implementation and design of a database is highly situational and there is no set "rule" that you can apply to every design. Limiting the design to 4 joins can potentially kill you if you have a very complex data mart and it can potentially hurt you if you are trying to create a highly responsive OLTP database. With every gain, you must sacrifice something. As for documentation on joins, there isn't too much out there (except lots of opinions), but I do know that the TPC-D benchmark queries have joins that range from 3-8 tables (and since Oracle can do those very efficiently, I would say that an 8 table join should be child's play for Oracle in most situations). Anyways, here is a link to a description of those TPC-D queries:

Jeffery Stevenson
Chief Database Geek
Medical Present Value, Inc.
Austin, TX

-----Original Message-----
From: Diana Duncan [] Sent: Wednesday, October 25, 2000 1:05 PM To: Multiple recipients of list ORACLE-L Subject: RE: Re[2]: Re[2]:Upgrading Oracle

Rachel wrote
"...a third party product that
denormalizes tables because their "experts" told them that if you have more than 4 tables in a join performance suffers significantly."

This is something I get from our chief architect on a regular basis, and he won't believe me when I say it all depends on the size of the tables, the indexes on the tables and the query criteria. He is dead set that the number of tables in a join determines the performance of the query. Does anyone know where I can find a concrete explanation that I can have him read to disabuse him of this notion?


Please see the official ORACLE-L FAQ:

Author: Diana Duncan

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message to: (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Oct 25 2000 - 13:52:26 CDT

Original text of this message