| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Datawarehouse suggestions?
sit on top of? as, in the same database?
The data for the warehouse does not come directly from the OLTP database, but instead comes from programs reading/extracting from web logs and another database (redbrick) that logs page hits.
I'm hoping to persuade them to eventually purge old data, or at least roll it up into summary tables. But hash partitions sounds good for the beginning 'cause it will be at least 2 years before I can even begin to persuade anyone to purge data
>From: Joe Testa <teci_at_the-testas.net>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Re: Datawarehouse suggestions?
>Date: Sun, 02 Sep 2001 04:30:22 -0800
>
>ÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌ
>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).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: carmichr_at_hotmail.com 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 - 08:57:27 CDT
 |
 |