Re: Datawarehouse suggestions?

From: Joe Testa <teci_at_the-testas.net>
Date: Sun, 02 Sep 2001 04:35:45 -0700
Message-ID: <F001.003805A6.20010902043022@fatcity.com>

ÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌ
X-Declude-Sender: teci_at_the-testas.net [63.26.83.125]

X-Declude-Spoolname: D182f228.SMD
X-Note: This E-mail was sent from 1cust125.tnt1.circleville.oh.da.uu.net. ([63.26.83.125]).
X-Note: Please send abuse reports to vei-abuse_at_vei.net.

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 :)

joe

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 http://explorer.msn.com/intl.asp
>

-- 
Joe Testa  
Performing Remote DBA Services, need some backup DBA support?
For Sale: Oracle-dba.com domain, its not going cheap but feel free to
ask :)
IM: n8xcthome or joen8xct
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Joe Testa
  INET: teci_at_the-testas.net

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: ListGuru_at_fatcity.com (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