table columns storage

[utkanbir]

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

[Erland Sommarskog]

utkanbir (ho**********@yahoo.com) writes:

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?

In SQL Server, you cannot have more than 1024 columns per table.

But since this is a data warehouse, you should probably look at Analysis
Services. Unfortunately, I know next to nothing about Analysis services,
so I can not give much help there.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp