Re: Number of Columns in a Table & SQL Performance ?
- From: John Kanagaraj <john.kanagaraj@xxxxxxxxx>
- To: mwf@xxxxxxxx
- Date: Mon, 9 Mar 2009 22:02:08 -0700
Vivek/all,
> 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 **
--
//www.freelists.org/webpage/oracle-l
Other related posts:
