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

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Greg Rahn Sent: Monday, March 09, 2009 5:22 PM
To: VIVEK_SHARMA_at_infosys.com
Cc: ORACLE-L
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_at_infosys.com> wrote:
> 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..

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l