RE: Number of Columns in a Table & SQL Performance ?
Date: Mon, 9 Mar 2009 19:53:14 -0400
Is the existing relationship between the two tables truly 1 to 1 and if the physical layer of implementation was not an issue would you in fact claim the two tables actually represent a single relation split into two pieces?
Only then would I even consider jambing them together. A four hundred column relation is a bit tough for me to grok. I suppose they exist, since the universe is probably infinite. Now, presuming you pass that hurdle, it would become a question of whether the advantage having a single index for the instantiation of the primary key and any other indexes for which you now have two plus the removal of the need to perform the join of the physically separated portions of a tuple when you needed simultaneous access to columns from the divided halves of the relation in a single query as it stands in the current state is more or less expensive overall.
If you allow nulls in any of the columns, there is the added issue of whether from the known relation (that is to say the enumerated tuples of the existing two table representation of the relation), is there an ordering of columns ordered such that you can maximize the consecutive trailing null column values? Merging the current physical separation could eliminate existing trailing null column frequencies that allow rows to fit in fewer blocks (presumably you have many continued rows with that many columns unless you have many consecutive row values that are null).
So if indeed it really is a single relation, then there certainly is something to measure for each such relation to determine whether there is a possibly more efficient physical structure in Oracle to represent it.
I have my doubts that either table is already normalized, but that's just me musing.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of VIVEK_SHARMA
Sent: Monday, March 09, 2009 9:20 AM
Subject: Number of Columns in a Table & SQL Performance ?
Does the number of columns in a table affect the performance of SELECT/INSERT/UPDATE in OLTP Transactions.
If so, why?
NOTE - Merge of 2 existing tables each has 200 columns approx into a single Table is being planned..
Thanks in Advance
- CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of the addressee(s). If you are not the intended recipient, please
notify the sender by e-mail and delete the original message. Further, you are not
to copy, disclose, or distribute this e-mail or its contents to any other person and
any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
every reasonable precaution to minimize this risk, but is not liable for any damage
you may sustain as a result of any virus in this e-mail. You should carry out your
own virus checks before opening the e-mail or attachment. Infosys reserves the
right to monitor and review the content of all messages sent to or from this e-mail
address. Messages sent to or from this e-mail address may be stored on the
Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***Received on Mon Mar 09 2009 - 18:53:14 CDT