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-----
From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 10, 2003 3:19 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Data Warehouse configuration
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.
[EMAIL PROTECTED]
-----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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
PHYSICAL DATABASE CHECKLIST - DSS.htm
Description: Binary data
DESIGN OF ODS LOAD2.doc
Description: MS-Word document
