| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re[2]: Re[2]:Upgrading Oracle
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:
http://www.tpc.org/articles/TPCDart1.97.html
Jeffery Stevenson
Chief Database Geek
Medical Present Value, Inc.
Austin, TX
-----Original Message-----
From: Diana Duncan [mailto:Diana_at_fileFRENZY.com]
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?
Thanks,
Diana
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Diana Duncan
INET: Diana_at_fileFRENZY.com
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: ListGuru_at_fatcity.com (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
 |
 |