Re: 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.
> NOTE - Merge of 2 existing tables each has 200 columns approx into a single
> Table is being planned..

Please note the following little snippet from the Concepts manual:

Row Format and Size
Oracle stores each row of a database table containing data for less than 256 columns as
one or more row pieces. If an entire row can be inserted into a single data block, then
Oracle stores the row as one row piece. However, if all of a row’s data cannot be
inserted into a single data block or if an update to an existing row causes the row to
outgrow its data block, then Oracle stores the row using multiple row pieces. A data
block usually contains only one row piece for each row. When Oracle must store a row
in more than one row piece, it is chained across multiple blocks.

When a table has more than 255 columns, rows that have data after the 255th column
are likely to be chained within the same block. This is called intra-block chaining. A
chained row’s pieces are chained together using the rowids of the pieces. With intra-block chaining, users receive all the data in the same block. If the row fits in the
block, users do not see an effect in I/O performance, because no extra I/O operation is
required to retrieve the rest of the row.

HOWEVER: If there are 400 columns, I would bet that most rows will not fit in one block and hence you will see a lot more 'db file sequential read' than normally required. As well, I remember that Steve Adams (or someone long ago) mentioning that there is an additional cost for accessing a column "further down the list" - sorry don't have that link.

-- 
John Kanagaraj <><
http://www.linkedin.com/in/johnkanagaraj
http://jkanagaraj.wordpress.com (Sorry - not an Oracle blog!)
** The opinions and facts contained in this message are entirely mine
and do not reflect those of my employer or customers **
--
http://www.freelists.org/webpage/oracle-l