tables storage technique?

Hi , This is related to datawarehouse , data mining . We are told that data mining tools such as spss or sas , need a large table which has lots of columns inside . Based on our project , out final table which will be used in data mining , has lots of computed values . The final number of columns of the table is 7800!(lots of calculated values) First of all , i dont see the reason of this requirement of the data mining tools. I will be appreciated if someone can clarify this: Why does a data mining tool need such a large table? The main issue is if i create table with 7800 columns inside ( in fact oracle only allows to create table with 1000 columns) , i believe it cant be queried. My basic calculation shows that the average row size of this table will be 160kb. , considering my db_block_size of 16kb. this means 10 blocks for 1 row. (The table will have more than 10.000.000 rows) No matter how fast my disk subsystem is , i think the queries against this table will fail. So what can i do? May be I need a different type of storage technique for instance column based storage ( i heard that sysbase has this feature , dont know the details / purpose..) How can i solve such a problem? The database server really does not matter , it can be oracle,sqlserver , sybase,informix , etc... I will be appreciated if someone can help me abut the issue. Kind Regards, hope

utkanbir wrote: > This is related to datawarehouse , data mining . We are told that data > mining tools such as spss or sas , need a large table which has lots > of columns inside . Based on our project , out final table which will > be used in data mining , has lots of computed values . The final > number of columns of the table is 7800!(lots of calculated values) > First of all , i dont see the reason of this requirement of the data > mining tools. I will be appreciated if someone can clarify this: Why > does a data mining tool need such a large table? > > The main issue is if i create table with 7800 columns inside ( in fact > oracle only allows to create table with 1000 columns) , i believe it > cant be queried. > My basic calculation shows that the average row size of this table > will be 160kb. , considering my db_block_size of 16kb. this means 10 > blocks for 1 row. (The table will have more than 10.000.000 rows) No > matter how fast my disk subsystem is , i think the queries against > this table will fail. So what can i do? May be I need a different > type of storage technique for instance column based storage ( i heard > that sysbase has this feature , dont know the details / purpose..) > How can i solve such a problem? The database server really does not > matter , it can be oracle,sqlserver , sybase,informix , etc... I will > be appreciated if someone can help me abut the issue. This question was also posted to comp.databases.oracle.server and comp.databases.ibm-db2. Informix allows rows up to 32KB wide - so your columns would have to be about 4-bytes each (integers?) to fit. That applies in the table proper, and in the result set. The blob types (BYTE, TEXT, BLOB, CLOB) count as something like 56 bytes each towards the limit, but can themselves be extremely large. Frankly, I doubt that many brains could comprehend 7000+ derivative facts about the key data for a table -- I'm sceptical of any design that calls for so many columns, whether stored or computed. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/