Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US