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

  • From: "Powell, Mark D" <mark.powell@xxxxxxx>
  • To: <greg@xxxxxxxxxxxxxxxxxx>, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 10 Mar 2009 09:49:20 -0400

While I do not love working with tables that have 200 plus columns we
have several and the tables are fully normalized.  The number of
attributes depends on what is being modeled and is in itself not an
indication of the degree of normalization present in the design.

-- Mark D Powell --
Phone (313) 592-5148

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Greg Rahn
Sent: Monday, March 09, 2009 5:22 PM
To: VIVEK_SHARMA@xxxxxxxxxxx
Subject: Re: Number of Columns in a Table & SQL Performance ?

First I have to ask myself, what (good) relational database design for
an OLTP system would lead normalized tables having 200 columns.

Second, I would think about buffer cache efficiencies.  The wider the
table the less rows that fit in a block.  Given that most OLTP
applications rarely need access to all the columns in a table it would
seem to me that having a 200, let alone 400 column table would lead to
having lots of data in the buffer cache that is not used and thus result
in either a reduced buffer cache efficiency (causing more than desired
physical I/O) or having to compensate by having a very large db cache
full of rows whose columns are not accessed.

Perhaps you could share what are the perceived benefits from merging the
two tables?

On Mon, Mar 9, 2009 at 6:19 AM, VIVEK_SHARMA <VIVEK_SHARMA@xxxxxxxxxxx>
> Does the number of columns in a table affect the performance of 
> If so, why?
> NOTE - Merge of 2 existing tables each has 200 columns approx into a 
> single Table is being planned..


Other related posts: