Thanks for the quick and helpful response.

I need little more clarification.

1. What if we do not retrieve all columns all the time i.e instead of using the view all the time, rewrite code to query only the related table (or tables) and use the view only when all columns are needed (Lets say we use the view 40% and indvidual tables 60%).

2. If we go with one big table solution, what should be the ideal PCTFREE and PCTUSED if this table DML pattern is:

- 90% inserts
- 10% deletes
- no updates.

thanks
Farhan

Why do you need 900 columns? I read and re-read your question to Tom trying to figure out why but I don't see it. How in the world are you going to use a 900 column wide table? Are you actually going to fit that many columns in a report?

Is there not another way to store the data? Maybe like this:

create table summary_header
(summary_id number primary key not null);

create sequence summary_id_seq;

create or replace trigger t_summary_header_bi
before insert
on summary_header referencing old as old new as new
for each row
begin

if :new.summary_id is null then
select summary_id_seq.nextval
into :new.summary_id
from dual;
end if;

end t_summary_header_bi;


create table attribute_header
(attribute_id number primary key not null);

create sequence attribute_id_seq;

create or replace trigger t_attribute_header_bi
before insert
on attribute_header referencing old as old new as new
for each row
begin

if :new.attribute_id is null then
select attribute_id_seq.nextval
into :new.attribute_id
from dual;
end if;

end t_attribute_header_bi;


create table summary_data
(summary_id number not null,
attribute_id number not null,
attribute_value varchar2(255) not null);

alter table summary_data add primary key (summary_id, attribute_id);

alter table summary_data add foreign key (summary_id) references summary_header (summary_id);

alter table summary_data add foreign key (attribute_id) references attribute_header (attribute_id);

Well, I don't like the model too much either so I added the attribute_header so you couldn't add any attributes that aren't defined up front. Hopefully, that would limit it to the 900 attributes. I do like my solution better than having 900 columns. I just don't see any value to having a table that wide.

But using XML is certainly another way to go. I guess my point is, there are other ways to solve the problem and the other ways are more than likely better.

The simple question to the Farhan is why? Why do you need 900 columns? Can't you break this up into either a normalized model or even a star schema? And as Tom pointed out, XML is another choice too.

Or you can list-partition one 90-column table by 10 logical column sets (instead of having 10 separate tables). If you (generally) don't need to look at more than one partition (logical columns set) at a time - it probably would work for you.

Would a cluster work in this situation? Break up the table as Tom suggested, keeping the most commonly accessed columns together...or even grouping the columns by those most likely to be accessed together.

There is some value for going down with the data over more columns. A simple example is the need to add 10 additional fields. With the 900 column table, you would have to add 10 additional columns and then change whatever code you have to account for these additional columns.

If you knew up front that your elements might be changing, you most certainly would add/delete records from a table rather than having additional columns.

A good example is v$parameter. Every time Oracle modifies the parameters, they simply insert/delete records rather than adding/deleting columns.

Can you give an example where you would want a table with 900 columns? I have never seen an example of this. I've seen some people try by having a bunch of columns like code1, code2, code3, code4 rather than having a simple table that has a foreign key to another table and a code column. That has always been better in my experience because people always wanting to add or remove the number of elements and adding/removing columns can be rather painful compared to simple DML.

Last try and then I will leave you alone. :)

Let's say the data consists of sales data. Each row will be a summary of sales for a single sales person. The company sells products in 9 states and there are 10 elements that rate the performance of the sales person for each state.

The de-normalized table would look like this:

create table monster
(employee_id number not null,
ny_total number not null,
ny_avg_daily number not null,
ny_avg_monthly number not null,
ny_blah_daily number not null,
ny_blah_monthly number not null,
ny_foo_daily number not null,
ny_foo_monthly number not null,
ny_bar_daily number not null,
ny_bar_monthly number not null,
ny_grant_total number not null,

nj_total number not null,
nj_avg_daily number not null,
nj_avg_monthly number not null,
nj_blah_daily number not null,
nj_blah_monthly number not null,
nj_foo_daily number not null,
nj_foo_monthly number not null,
nj_bar_daily number not null,
nj_bar_monthly number not null,
nj_grant_total number not null,

ak_total number not null,
ak_avg_daily number not null,
ak_avg_monthly number not null,
ak_blah_daily number not null,
ak_blah_monthly number not null,
ak_foo_daily number not null,
ak_foo_monthly number not null,
ak_bar_daily number not null,
ak_bar_monthly number not null,
ak_grant_total number not null,

al_total number not null,
al_avg_daily number not null,
al_avg_monthly number not null,
al_blah_daily number not null,
al_blah_monthly number not null,
al_foo_daily number not null,
al_foo_monthly number not null,
al_bar_daily number not null,
al_bar_monthly number not null,
al_grant_total number not null,

--etc for 9 states in total to get 901 columns in my table.

So now, the company grows and adds two more states. This would require DDL to add 20 columns to the table, the ETL code would have to change to handle these new columns, and reports would have to change to handle the new columns.

The funky way to model this would be this:

create table funky
(employee_id number not null,
state_code varchar2(2) not null,
total number not null,
avg_daily number not null,
avg_monthly number not null,
blah_daily number not null,
blah_monthly number not null,
foo_daily number not null,
foo_monthly number not null,
bar_daily number not null,
bar_monthly number not null,
grant_total number not null);

The addition of two more states would be more rows to the table. I would also have a state_code table that has all of the acceptable state codes with a foreign key from funky to the state code lookup table.

Which table is "better"?

We have a table such as the "funky" table in our OLTP. It is great for those guys. They merely add elements and they are off. It is terrible for the reporting side (were I am). It is slow and you always have to pivot the data so that it is one row.

If you had particular reports that used a subset of the 900 columns, and you found a measurable and worthwhile performance benefit to isolating these columns into their own table, then I'd suggest that a Materialized View with Query Rewrite could be used to make a copy of them in a new table - ie. keep everything in the 900 column table and create appropriate MV's for the subsets that you need (possibly aggregating also, for extra goodness)

Yes, our model is somewhat simliar to that 'state' example but with two big differences.

1. The domain is static - no inserts/updates allowed to that domain.
2. The attributes required for each domain value are different. (There are about 40% common attributes)

One more clarification: This table is not an OLTP table, it is a denormalized summary statistics captured for one report i.e. One row in this table represent the entire report (The report shows matrices of summary values - no details).

We did thought about breaking this summary table into a header table and a domain specific detail table (that will contain 10 rows for each header i.e. a row for each domain, and will have uninon of all attributes of all domains). But, what do you think about performance of querying 10,000 'moster' rows versus pivoting 10 rows for each report thus accessing 10,000 header + 10 x 10,000 detail = 110,000 'small' rows?

Hi Tom,
We are using Siebel crm application and we have two tables in the application that have more then 255 columns - (S_ORG_EXT - 374 and S_ASSET - 296). We are using 8K block size in the database.

In your response above you mentioned that anything over 255 columns will be chained.

In Siebel crm everthing is driven from account (s_org_ext) table (ie Most of the times user queries account related information eg. account's activity, service request, notes, opportunities etc..)

Could you please let me know what are the performance implications?

As always, appreciate your feedback.
Thanks

>>2) anything over 255 columns will be chained.

Is the chaining depending on the data size and available space on the block? Why is it depending on number of columns? What if I have a 300 columns table but I only used first 2 number columns? Still is it going to do the chaining?

How about if I add data to last 2 columns in a table with 300 columns?

I've seen this kind of attribute driven tables in lot of client places and I told them not to make it complicated if you don't have too. These kind of Attributes driven tables are for Software Products like Oracle Apps.. where they need a flexible data model for different places,different needs... Thanks

Let me give you some examples of tables that require lots of columns:

1. We are implementing multi-lingual Oracle Forms. We've played around with Oracle's various translation tools and concluded that they impose too much overhead - we need one version of the Form and the ability to make it change language at will.

We are doing this using a table with 1,000 columns. The first few columns indicate the Form, the Language, and a few other things. The remaining columns are 900+ Form text items - labels, tool tips, etc.

By keeping them all in one column we bring them back with a single select and can automatically load them right into the labels with no extra coding. This has a major performance benefit.

As second use is something similar to Oracle Applications' Flexfields. Our analysis of our customers is that almost all of them are satisfied with three languages for data. We have one customer who requires four languages. So we're going to implement a system that supports 5.

We need configurable columnslike the SEGMENT_xx columns in Oracle Applications but we need to support multi-lingual data and also configurable image fields. Therefore our configurable field sets are going to have a bit more than 180 columns in them:

FIELD_[00-30]_L[1-5], FIELD_[00-30]_BLOB, and a few housekeeping items like a hashed concatenation of all FIELD_xx_Lx for each language to index when the configurable Field Set must be Unique.

Unlike Oracle we don't distinguish between KEY configurable fields and non-KEY configurable fields. We just implement two field sets - one for KEY, one for non-Key.

If we decide to use a FIELD SET to implement application level functionality then we will also provide another FIELD SET for user level functionality.

What this all means is that some of our tables will have almost 600 columns that are just used to define FIELD SETS. That is in addition to any other columns they may have.

(And yes, we did think about splitting this out into separate rows for each language but past experience is that this ends up being much harder to manage. For example, forcing the user to use at least one language in every row becomes very difficult.)