Re: Number of Columns in a Table & SQL Performance ?
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: VIVEK_SHARMA@xxxxxxxxxxx
- Date: Mon, 09 Mar 2009 21:12:04 +0100
Vivek,
200 columns look to me like too much - much too much. I'll revise
design first. If you have many null columns, consider some type of
inheritance. Even in a complex application, it's extremely rare that you
manage this number of attributes for all items under management. Get
your inspiration from the Oracle data dictionary (sys.obj$ that holds
type, name, owner, etc. for all possible objects and then one table per
object type that only stores what is relevant to this particular type).
Index issues are obvious for inserts and deletes, and updates to a
lesser extent, because I still have to see a super-wide table that
hasn't a crazy number of indexes; and even if you are careful it's
likely that someone someday will not be. But think also that you will
get a very low data density in your blocks (few rows per block), which
means that any scan will be extremely costly in terms of number of
blocks read (whether they are read in memory or from the disks). I'll
avoid mentioning row migration if you ever need to partition your table.
HTH
S Faroult
VIVEK_SHARMA wrote:
>
> 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
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
