Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: Datawarehouse suggestions?

From: Joe Testa <>
Date: Sun, 02 Sep 2001 04:35:45 -0700
Message-ID: <>

X-Declude-Sender: []
X-Declude-Spoolname: D182f228.SMD
X-Note: This E-mail was sent from ([]).
X-Note: Please send abuse reports to

How about this as a concept:

having your "datawarehouse" sit on top on the oltp(which is think is what you're trying to do, on a side note, i've always wanted to do something like that and write a paper on it, but i'm back now) using materialized views(aka as snapshots), refreshed nightly via materialized view logs.

Since you also wont bepruging data , i'd hash partition the materialized view(s).

how's that for a start :)


Rachel Carmichael wrote:
> 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.
> question:
> 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?
> Thanks
> Rachel
> _________________________________________________________________
> Get your FREE download of MSN Explorer at

Joe Testa  
Performing Remote DBA Services, need some backup DBA support?
For Sale: domain, its not going cheap but feel free to
ask :)
IM: n8xcthome or joen8xct
Please see the official ORACLE-L FAQ:
Author: Joe Testa

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 Sun Sep 02 2001 - 06:35:45 CDT

Original text of this message