Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Datawarehouse suggestions?

Datawarehouse suggestions?

From: Rachel Carmichael <>
Date: Sat, 01 Sep 2001 20:28:30 -0700
Message-ID: <>

Okay, first the rant.... they want me to build a new database to hold logs of every time someone hits a page on our website or uses one of the functions on the site. Without a new server or disk, on a box that currently holds the OLTP database, on a disk array that is RAID 5 and that EMC wants to take BACK disks from. In other words, they want magic. Where did I leave that rabbit and hat?

end rant, here's the setup:

Conservative estimate says that one of the two main tables (at least for now) will grow to a GB in a year or two, the other will hit 500M in that timeframe. Rough estimates of # of rows is 273M for the first table, 70M for the second over 2 years. Rows are < 200 bytes at max. There will be several small lookup tables and the number of logging tables will increase over time.

They are NOT talking about purging data, ever. And we are hoping that the volume INCREASES, not decreases or remains the same, which means the space estimates are no more realistic than throwing a dart at a board and picking a number.

Oh yeah, did I mention that they want this to be a reporting database for external customers (read revenue stream) and want these external people to be able to query on any combination of columns -- so they want indexes on EVERY column.

I will have a nightly maintenance window for loads of prior day's data. I'm thinking to do a shutdown, change the init.ora to tune for heavy batch, startup, drop indexes, load data, recreate indexes, shutdown, backup, startup with init.ora tuned for querying.


I've always worked on either batch reporting databases or OLTP, this will be the first semi, sort of data warehouse for me. Advice please on how to build this, as I am doing this one from scratch and can plan it.

I'm thinking:

database will be 8.1.7, Solaris 2.7

rbs and temp tablespaces as LMTs, remaining tablespaces dictionary-managed

db_block_size 16K

a "large table" tablespace with initial/next at 10M a "large index" tablespace as above

a "small table" tablespace with initial/next at 16K a "small index" tablespace as above

possibly partitioning (I have to check our licenses) on the timestamp field.

Any suggestions, gotchas, "this is WRONG, here's how to do it" comments?



Get your FREE download of MSN Explorer at


Please see the official ORACLE-L FAQ:

Author: Rachel Carmichael

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message to: (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Sat Sep 01 2001 - 22:28:30 CDT

Original text of this message