comparison : table with 600 columns(varchar2) vs. 6 tables of 100 columns(varchar2)

[amishjc]

I'm using sun Os 5.6 and Oracle enterprise edition 8.1.6.3
working on scalability and performance.

We are going to have a client data with 600 attributes[all of them varchar2(255)]. The data volume would be approximately 50 million records. We will have first time INSERT and then almost UPDATE/DELETE every 15 days.

We are looking for the optimum performance.

should we create one table with all 600 attributes in it
OR
should we have 6 tables with 100 attributes each and join them to get compelete data.

Please post ur concerns or experience for this kind of design.

Thanks

Amish

[slimdave]

Ah, vertical partitioing.

If you can logically divide the columns up into different groups in different tables, so that updates and selects would tend to be against only one or two of the tables, then vertical partitioning may work for you.

In general though, you're going to be wanting a big block size, to minimize row chaining, and you'll be wanting to think carefully about your pctfree value in order to minimize row migration. Almost certainly you'll have to check for migration issues periodically.

I must say that thedesignsounds suspicious -- so many varchar2(255)'s makes it sound like these aren't very neat attributes, or that not-a-lot of thought has gone into the true column widths.

[JMac]

not-a-lot of thought has gone into the true column widths

Be careful there slimdave! I was reprimanded by a development project manager for saying that the fact that processing of 30 rows in a million row table was taking an hour (and not a minute) was due to "inappropriate initial system design (and/or sloppy application code)". Unused Primary Keys, unindexed searching and updates ... How dare I say that!

I mean how the hell are you supposed to say these things to developers? (This could be a serious thread of its own)

[amishjc]

SlimDave/JMac,

Thanks for ur answers, we will consider those points before we create table for sure.

I wanted to know

1. known issues/bugs for a table having more than 255 columns on 8i
2. Performance comparison for a table with 600 cols vs 6 tbls of 100 cols
3. any good/bad experience with this kind of design


Amish

[slimdave]

Originally posted by amishjc
1. known issues/bugs for a table having more than 255 columns on 8i
2. Performance comparison for a table with 600 cols vs 6 tbls of 100 cols
3. any good/bad experience with this kind of design

1. search metalink -- haven't heard of anything myself

2. 600 col table, make sure that the frequently accessed/updated columns are first in the column list. 6x100 cols, joins will be the Big enemy -- my previous advice was aimed at reducing the number of times you have to join these tables together.
3. Still have reservations about the table/column design -- i suspect shoddy design work -- sorry JMac, just had to say it