Oracle DBA Guide to Data Warehousing and Star Schemas

  • ISBN13:


  • ISBN10:


  • Edition: 1st
  • Format: Paperback
  • Copyright: 2003-06-04
  • Publisher: Prentice Hall

Note: Supplemental materials are not guaranteed with Rental or Used book purchases.

Purchase Benefits

  • Free Shipping On Orders Over $35!
    Your order must be $35 or more to qualify for free economy shipping. Bulk sales, PO's, Marketplace items, eBooks and apparel do not qualify for this offer.
  • Get Rewarded for Ordering Your Textbooks! Enroll Now
List Price: $49.99 Save up to $12.50
  • Buy Used
    Add to Cart Free Shipping


Supplemental Materials

What is included with this book?


Oracle DBA Guide to Data Warehousing and Star Schemasis the definitiveguide to maximizing the performance, flexibility, and manageability ofany Oracle 8i/9i data warehouse. Legendary Oracle data warehouse expertBert Scalzo offers hard-won lessons and proven techniques for designing,constructing, tuning, and maintaining star schema data warehouses inreal production environments. Coverage includes: planning hardware andsoftware architectures; star schema design; data loading; queryoptimization; partitioning; administration; and much more.

Author Biography

Bert Scalzo is a product architect for Quest Software

Table of Contents

Acknowledgmentsp. xvii
Introductionp. xix
What Is a Data Warehouse?p. 1
The Nature of the Beastp. 2
Data Warehouse vs. Big Databasep. 4
Operational Data Stores Don't Countp. 7
Executive Information Systems Don't Countp. 9
Warehouses Evolve without Phasesp. 10
The Warehouse Roller Coasterp. 12
Software Architecturep. 13
Business Intelligence Optionsp. 15
Oracle Version Optionsp. 17
Oracle Instance Options--Queryingp. 22
Oracle Instance Options--Loadingp. 26
Recommended Oracle Architecturep. 32
Great Operating System Debatep. 33
The Great Programming Language Debatep. 35
The Serial vs. Parallel Programming Debatep. 35
Hardware Architecturep. 41
Four Basic Questionsp. 42
How Many CPUs?p. 43
How Much Memory?p. 51
How Many of What Disks?p. 53
Recommended Hardware Architecturep. 58
The Great Vendor Debatep. 60
The 32- vs. 64-Bit Oracle Debatep. 61
The Raw vs. Cooked Files Debatep. 62
The Need for Logical Volume Managersp. 64
Star Schema Universep. 67
The Rationale for Starsp. 68
Star Schema Challengesp. 69
Modeling Star Schemasp. 71
Avoid Snowflakesp. 73
Dimensional Hierarchiesp. 76
Querying Star Schemasp. 78
Fact Table Optionsp. 79
When Stars Implodep. 83
Tuning Ad-Hoc Queriesp. 85
Key Tuning Requirementsp. 86
Star Optimization Evolutionp. 87
First-Generationp. 88
Second-Generationp. 89
Third-Generationp. 90
Fourth-Generationp. 93
Evolutionary Summaryp. 95
Star Transformation Questionsp. 96
Initialization Parametersp. 99
Star Schema Index Designp. 102
Cost-Based Optimizerp. 108
Some Parting Thoughtsp. 109
Loading the Warehousep. 113
What About ETL Tools?p. 114
Loading Architecturep. 121
Upstream Source Datap. 123
Transformation Requirementsp. 125
Transform, Then Loadp. 126
Scenario 1p. 127
Scenario 2p. 131
Scenario 3p. 134
Scenario 4p. 136
Scenario 5p. 138
Load, Then Transformp. 142
Deploying the Loading Architecturep. 146
Implementing Aggregatesp. 147
What Aggregates to Build?p. 148
Loading Architecturep. 150
Aggregation by Itselfp. 153
Scenario 1p. 156
Scenario 2p. 159
Scenario 3p. 162
Use Materialized Viewsp. 163
Partitioning for Manageabilityp. 169
A Plethora of Design Optionsp. 170
Logical Partitioning Designp. 172
Simple Partitioning in 8ip. 175
Simple Partitioning in 9ip. 177
Complex Partitioning in 8ip. 179
Complex Partitioning in 9ip. 183
Partition Option Benchmarksp. 187
Operational Issues and Morep. 189
Backup and Recoveryp. 190
Space Managementp. 192
Extent Managementp. 200
Updates and Patchesp. 201
Indexp. 205
Table of Contents provided by Ingram. All Rights Reserved.


I''ve written this book with the hope that it will serve as mylifetime technical contribution to my database administrator (DBA)brethren. It contains the sum knowledge and wisdom I''ve gathered thispast decade, both working on and speaking about data warehousing. Itdoes so purely from the DBA''s perspective, solely for the DBA''s needsand benefit. While I''ve worked on many data warehousing projects, my three yearsat Electronic Data Systems (EDS) as the lead DBA for 7-ElevenCorporation''s enterprise data warehouse provided my greatest learningexperience. 7-Eleven is a world leader in convenience retailing, withover 21,000 stores worldwide. The 7-Eleven enterprise data warehouse: Is multi-terabyte in size, with tables having hundreds of millionsor billions of rows. Is a true star schema design based on accurate business criteria andrequirements. Has average and maximum report runtimes of seven minutes and fourhours, respectively. Is operational 16X6 (i.e. the database is available 16 hours perday, 6 days per week). Has base data and aggregations that are no more than 24 hours old(i.e., updated daily). While the 7-Eleven enterprise data warehouse may sound impressive,it was not that way from Day One. We started with Oracle 7.2 and a smallHewlett#x13;Packard (HP) K-class server. We felt like genuine explorers aswe charted new territory for both EDS and 7-Eleven. There were fewreference books or white papers at that time with any detailed datawarehousing techniques. Plus, there were few DBAs who had alreadysuccessfully built multi-terabyte data warehouses with whom to network.Fortunately, EDS and 7-Eleven recognized this fact and embraced thetruly iterative nature of data warehousing development. Since you are reading this book, it''s safe to assume we can agreethat data warehousing is radically different than traditional onlinetransaction processing (OLTP) applications. Whereas OLTP database andapplication development is generally well-defined and thus easy tocontrol via policies and procedures, data warehousing is more iterativeand experimental. You need the freedom, support, and longevity tointelligently experiment ad-infinitum. With few universal golden rulesto apply, often the method of finding what works best for a given datawarehouse is to: Brainstorm for design or tuning ideas. Add those ideas to a persistent list of ideas. Try whichever ideas currently look promising. Record a history of ideas attempted and their results. Keep one good idea out of 10-20 tried per iteration. Repeat the cycle with an ever growing list of new ideas As Thomas Peters states, "Life is pretty simple: You do some stuff.Most fails. Some works. You do more of what works." That''s some of thebest advice I can recommend for successfully building a data warehouseas well. Purpose There are numerous data warehousing books out there, so why is thisone different? Simply put: its DBA focus on implementation details. Infact, the mission statement for this book is: To serve as the DBA''s definitive and detailed reference regardingthe successful design, construction, tuning, and maintenance of starschema data warehouses in Oracle 8i and 9i. So how is this different from what''s already out there? In general,I''ve found that most data warehousing books fall into one of threecategories: Conceptual--Primarily educational about theories andpractices, with very high-level information Overview--Catalogs of hardware, software, and databaseoptions, with few specific recommendations Cookbook--Detailed, DBA-oriented advice for all the datawarehouse development lifecycle stages Respectively, "best-of-breed" examples for these three categoriesare: Data Warehouse Tool Kit: Practical Techniques for BuildingDimensional Data Warehousesby Ralph Kimball Oracle8 Data Warehousingby Gary Dodge and Tim Gorman This book, primarily since no other book exists with this kind ofdetailed DBA advice I mean no disrespect to these other categories or their books. Ihighly recommend Kimball''s book to anyone new to data warehousing. Anduntil such time as this books debuts, I also highly recommend Dodge''sbook for DBAs. Audience This book is intended for physical DBAs--period, end of story. Thisbook assumes an extensive and detailed working knowledge of Oracletechnologies. Moreover, it presumes a keen awareness of hardware andsoftware options--often a skill possessed only by DBAs who also serve asat least the backup operating system (OS) administrator as well. Thatsaid, there are chapters that will be both applicable and beneficial toother members of the data warehousing team. The sections on data modeling define how a DBA should interpret andextrapolate an entity relationship diagram (ERD) into a physicaldatabase design. So, this chapter would assist data modelers andapplication architects to understand how a DBA uses their input tocreate the underlying database structure. Likewise, the sections on staging, promoting, and aggregating datadefine how a DBA should manage objects and processes to mostexpeditiously load massive amounts of data. So, this chapter would beboth educational and inspirational to extract, transform, and load (ETL)programmers tasked with loading a data warehouse. And finally, the chapter on querying the data defines the indices,statistics, and plans necessary to deliver the best possible ad-hocquery runtimes. So, this chapter would assist business intelligencefront-end designers, who can appreciate how the database handles theircomplex, ad-hoc queries.

Rewards Program

Write a Review