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: Data Warehouse configuration

RE: Data Warehouse configuration

From: <Paula_Stankus_at_doh.state.fl.us>
Date: Mon, 10 Mar 2003 13:02:10 -0800
Message-ID: <F001.00565239.20030310130210@fatcity.com>


Also, I useful group relating to data warehousing issues similar to this is:

to <mailto:dwlist-request_at_datawarehousing.com> with the and www.dmreview.com

-----Original Message-----

Sent: Monday, March 10, 2003 3:56 PM
To: 'ORACLE-L_at_fatcity.com'

I just wrote a few things but here are some functions that definitely add to availability and lower maintenance and quicker recovery if implemented the correct way:

-partitioning - because you can use rolling partitions to move off data and
load new data.
-materialized views - because this allows the "smart" DBA to work behind the
scenes and gives the DBA most flexibility to work without affecting the users (on the base partitioned tables).
-the ability to generate aggregate data
-outlines and hints to ensure certain statements always act the same
-bitmapped indexes and bitmap merge (love it love it love it!!!)
-Suggest reading Ralph Kimballs' "Lifecycle Tooolkit" for the higher level
discussions on Star Schemas, project planning, politics.....
-Direct loads with ignore hints when you can do it
-Operational Data Store sometimes useful for doing pre-processing
-METADATA
-automate everything!!!!!!!!!!!!!

Here are some things I have been working on that I hope can be helpful to someone somewhere:

The Oracle 8i Data Warehousing Book was great - I imagine the 9i will be too.

-----Original Message-----

Sent: Monday, March 10, 2003 3:19 PM
To: Multiple recipients of list ORACLE-L

E

   I haven't seen anything. OFA is, in my recollection, primarily about putting files in standard locations so another DBA can come on-site and find stuff easily. Before OFA we all tended have our own preferences, since there were no guidelines.

   DW tends to be like OLTP, only more so ;-) You have to remember you are dealing with a lot more data, usually. You need to allow larger areas for landing areas for files that are transferred from another system for you to load. Just like OLTP, you want to get as many disk spindles involved as possible.

   Most data warehouses have two modes of operation, data refresh and queries. During the data refresh, you have the disk storing the data you will be loading, as well as the disks that will receive the data, and perhaps a temp space that will help rebuild indexes.

   Laying out disk for queries is much the same as OLTP systems, except you may have fewer critical queries to optimize for. Often OLTP systems are able to cache more of the data a query needs than you are able for DW queries.

   Another wrinkle is that after you've loaded the data, you may have a phase of building your materialized views.

   Which phase is the most important to optimize will depend on your DW situation, how large (in time) the loading window is.

   Another factor that will vary considerably between sites is the recovery time requirement.

   I don't know if the whole DW scene is less mature than OLTP, but there seems fewer rules of thumb for data warehouses. Or maybe it is just the nature of the beast.

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----

Sent: Monday, March 10, 2003 1:04 PM
To: Multiple recipients of list ORACLE-L

Are there any suggested recommendations for setting up the configuration of a data warehouse on new hardware? I understand the old OFA method (seperating data from indexes, etc). What I am wondering is if there are any recommendations specific to data warehouses that should be considered that will help improve performance, recovery, etc.?

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: E Richmond
  INET: cemail2_at_sprintmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services

---------------------------------------------------------------------
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.net
--

Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM
Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services

---------------------------------------------------------------------
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.net
--

Author:
  INET: Paula_Stankus_at_doh.state.fl.us

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services

---------------------------------------------------------------------
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 Mon Mar 10 2003 - 15:02:10 CST

Original text of this message

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