terabyte datamart questions

[upsylon]

On behalf of a friend (rich@stempniewicz.com) who needs this info and me, 'cause I'd like to know, too:

1. What is your experience with or understanding of Oracle when it comes to dealing with 1+ terabyte databases?

2. Do you know of any instances of such large db's running on Oracle and if so, what type of hardware were they running on? If yes, were they configured as OLTP or data warehouse's?

3. What is your experience with Oracle used for data warehouse / decision support system (dss) (as opposed to a oltp system)? (This really is the same question as #2, but would also include systems that may not have been very large) Do you have any experience with any of Oracle's OLAP tools?

4. Finally, have you (or any of your professional acquaintances) any exposure to a database called Teradata. (It comes from NCR and specializes in very large dss type db's).

5. What is your opinion about SQL Server being able to support a 1 terabyte system, let alone anything larger?

Some my friend's questions can be answered with a Yes or No, but we'd appreciate more expansives answers!

I've heard that we should employ clusters and partitioning and configure init.ora for dss use vs. oltp. I'd use bit-ma indexing, too, but am unsure of the consequences in terms of upkeep.

[tamilselvan]

1. What is your experience with or understanding of Oracle when it comes to dealing with 1+ terabyte databases?

Ans: Oracle can handle 1 TB database very smoothly

2. Do you know of any instances of such large db's running on Oracle and if so, what type of hardware were they running on? If yes, were they configured as OLTP or data warehouse's?

Ans: Certainly in OLTP environment Oracle scores very high mark, because its engine is built for that. Where as in DW, it is opposite.
Clustering tech was pioneered by IBM. Hence, you can see many DB2 systems running on clustering hardware. Oracle is weak on this type of hardware.
Many 3rd party consultancy companies asked Oracle to list out customers who run Oracle DW system with > 1 TB, but so far Oracle did not give the list. This indicates that many DW systems either run on Teradata or DB2.

3. What is your experience with Oracle used for data warehouse / decision support system (dss) (as opposed to a oltp system)? (This really is the same question as #2, but would also include systems that may not have been very large) Do you have any experience with any of Oracle's OLAP tools?

Ans: My experience with Oracle DSS is so far good. I can solve most of the business requirements with Oracle and Oracle OLAP tools.
Oracle OLAP tools came to market very late. First OLAP was built with RDBMS engine in SQL Server and then DB2.

4. Finally, have you (or any of your professional acquaintances) any exposure to a database called Teradata. (It comes from NCR and specializes in very large dss type db's).

Ans: I did not have experience with Teradata, but I know the largest DW running in Wal-Mart uses Teradata, as of now the DB size is over 100 TB. And the 2nd largest DW system is with K-Mart, its size is 92 TB on Teradata. My friend who has experience with Teradata told me that Teradata engine is built for DSS type of queries. Hence, Teradata is still running faster than Oracle.

5. What is your opinion about SQL Server being able to support a 1 terabyte system, let alone anything larger?

Ans: With the high end machines available cheaply, I feel SQL Server with its OLAP tool can capture DW market very quickly and definitely it can support 1TB DB. In fact SQL Server sets up the standard for OLAP, and now every other vendor follow it.