RE: Number of Columns in a Table & SQL Performance ?

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Mon, 9 Mar 2009 19:53:14 -0400
Message-ID: <CDD1E3F892DF498F828BB58B8AEA13F2_at_rsiz.com>



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.  

Regards,  

mwf      


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
To: ORACLE-L
Subject: Number of Columns in a Table & SQL Performance ?  

Folks  

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  

Vivek    

  • 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***

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Mar 09 2009 - 18:53:14 CDT

Original text of this message