Essential Oracle8i<sup><small>TM</small></sup> Data Warehousing: Designing, Building, and Managing Oracle<sup>®</sup> Data Warehouses,9780471376781

Essential Oracle8iTM Data Warehousing: Designing, Building, and Managing Oracle® Data Warehouses

by ;
ISBN13: 9780471376781
ISBN10: 0471376787
Format: Paperback
Pub. Date: 2000-09-01
Publisher(s): Wiley
Availability: This title is currently not available.
List Price: $74.90

Rent Book

Select for Price
Add to Cart
There was a problem. Please try again later.

New Book

We're Sorry
Sold Out

Used Book

We're Sorry
Sold Out

eBook

We're Sorry
Not Available

Summary

"This book is the definitive guide for serious Oracle8i professionals and is required reading for all Oracle data warehousing practitioners."-Shannon Platz, Senior Director, Business Intelligence & Warehouse Global Service Line, Oracle Corporation A complete hands-on guide to Oracle8i and earlier versions In this updated and expanded edition of their critically acclaimed Oracle8 Data Warehousing, Gary Dodge and Tim Gorman clearly explain everything you'll need to know to build and manage a large, high-performance data warehouse using Oracle8i. They provide a technical roadmap to the specific Oracle8 or Oracle8i features that are relevant to designing, building, tuning, and administering an Oracle data warehouse. After a brief review of the basic concepts, you'll find descriptions of the various hardware platforms to support the Oracle data warehouse. The authors then cover the Oracle features that can enhance a large data warehouse, the design considerations for a warehouse, and the steps necessary to load data into the warehouse. You'll also find out how to perform parallel operations using Oracle8 and Oracle8i to accomplish massive tasks more quickly. And you'll discover the specific features and techniques for implementing a distributed architecture. With this book, you'll learn how to: - Design a data warehouse for optimum performance - Construct the data warehouse using Oracle8 and Oracle8i database technology - Load data into the data warehouse - Summarize and aggregate data within a warehouse - Administer and monitor a data warehouse for optimum performance - Build and manage very large (multiterabyte) data warehouses Visit our Web site at www.wiley.com/compbooks/ Visit the companion Web site at www.wiley.com/compbooks/dodge for scripts, extensions, and additional material.

Author Biography

GARY DODGE has served in technical and managerial roles within Oracle Corporation since 1987. <BR>

Table of Contents

Foreword xvii
Acknowledgments xix
Introduction xxi
Audience xxii
Organization xxiii
What's on the Companion Web Site? xxv
An Invitation xxv
Data Warehousing
1(32)
What Is a Data Warehouse?
2(14)
A Data Warehouse Is Not a Project...
3(1)
...Rather, Data Warehousing Is a Process
4(1)
A Data Warehouse Is Not a Product...
5(4)
...Rather, Data Warehousing Is a New Way of Thinking about Data
9(5)
A Data Warehouse Is Not a Place...
14(1)
...Rather, Data Warehousing Is a Supplement to Traditional Data Processing
15(1)
Inmon's Four Characteristics of a Data Warehouse
16(9)
Subject-Oriented
16(2)
Integrated
18(1)
Nonvolatile
19(2)
Time Variant
21(4)
What Is an Operational Data Store?
25(1)
What Is a Data Mart?
26(1)
What Is OLAP?
27(1)
What Is Data Mining?
28(2)
Critical Success Factors in Data Warehousing
30(1)
Summary
31(2)
Hardware Architectures for Oracle Data Warehousing
33(46)
Computer Architectures
34(32)
Requirements Definition
34(1)
Evaluation Criteria
35(10)
Uniprocessor Systems
45(4)
Symmetric Multiprocessing (SMP) Systems
49(4)
Nonuniform Memory Access (NUMA) Systems
53(4)
Clustered Systems
57(4)
Massively Parallel Processing (MPP) Systems
61(4)
Summary: Computer Architectures
65(1)
Data Storage Architectures
66(10)
Software
66(7)
Hardware
73(3)
Summary
76(3)
Oracle Server Software Architecture and Features
79(66)
Oracle Server: External Architecture
80(22)
Oracle Database versus Oracle Instance
80(3)
File Architecture
83(7)
Memory Architecture
90(6)
Process Architecture
96(6)
Oracle Server: Internal Architecture
102(28)
Tablespaces
102(4)
Segments
106(16)
Extents
122(1)
Database Blocks
123(1)
Views
123(1)
Materialized Views
124(1)
The Oracle Data Dictionary
124(1)
Metadata
125(5)
Security Features
130(7)
Schemas and Users
131(1)
System Privileges
132(1)
Object Privileges
133(1)
Roles
133(2)
Auditing
135(1)
Virtual Private Databases
135(2)
Oracle Optimizer
137(2)
I/O Operations
139(1)
Sort Operations
140(1)
Backup and Recovery Features
140(2)
Oracle Server Options
142(1)
Summary
143(2)
Designing the Oracle Data Warehouse
145(76)
Users Need to Understand the Warehouse Structure and Operation
146(1)
Metadata
147(17)
Recording Warehouse Processing as Metadata
149(2)
What Metadata Does Oracle Provide?
151(3)
Delivering Metadata
154(10)
Introduction to a DW Case Study
164(3)
Warehouse Design Process
167(3)
Logical Warehouse Schema Design
170(17)
Detail and Summary Schema
170(4)
Star Schemas
174(10)
External and Reference Data
184(1)
Natural Keys versus Artificial Keys
184(3)
Physical Design
187(27)
Tablespace Design
187(3)
Partitioning Data
190(3)
Indexing
193(4)
Materialized Views
197(1)
Index-Organized Tables
198(1)
Temporary Tables
199(1)
Other Structures
200(1)
Database Constraints and Triggers
200(12)
Generating Unique Key Values
212(2)
Designing for High Availability
214(5)
What Is High Availability?
214(1)
Types of Interruptions
215(1)
Design Techniques for High Availability
216(3)
Balancing Design Objectives
219(1)
Summary
219(2)
Building the Oracle Data Warehouse
221(86)
ETT Tools for Building the Warehouse
222(3)
Using SQL*Plus to Create the Data Warehouse
225(15)
Keeping Logs of Your Actions
226(1)
SQL*Plus Formatting Commands
226(2)
Passing Values to a Script at Run-Time
228(2)
Writing Scripts for Execution in Batch
230(4)
Using SQL to Generate SQL
234(6)
Creating an Oracle Database for the Data Warehouse
240(17)
Estimating the Size of the Database
241(2)
Creating Tablespaces
243(14)
Creating the Schema
257(38)
Creating Tables
257(23)
Creating Index-Organized Tables
280(1)
Creating Temporary Tables
281(1)
Building Indexes for the Data Warehouse
282(10)
Oracle Database Clusters
292(3)
Analyzing Schema Objects
295(2)
Using Views in the Data Warehouse
297(7)
Using Views and DECODE for Complex Analyses
298(3)
Creating Materialized Views in the Data Warehouse
301(1)
Views, Materialized Views, or Summary Tables?
302(2)
Testing the Data Warehouse
304(2)
Summary
306(1)
Populating the Oracle Data Warehouse
307(96)
Dealing with Dirty Data
309(8)
Dealing with Missing Data
310(2)
Reconciling with ETT Tools
312(1)
Reconciling during Extract
313(3)
Reconciling during Load
316(1)
Loading Data
317(60)
Timing of Loads
318(1)
Using SQL*Loader
319(21)
Using Custom Load Programs
340(1)
Direct Load APIs
341(1)
Using Oracle's Gateway Products
342(1)
Extracting Data from Oracle Sources
343(34)
Post-Processing of Loaded Data
377(3)
Validating the Loaded Data
378(1)
Index Builds and Rebuilds
379(1)
``Publishing'' Data
380(4)
Purging and Archiving Warehouse Data
384(16)
Techniques for Archiving Data
386(3)
Issues with Archiving
389(3)
Techniques for Purging Data
392(8)
Summary
400(3)
Post-Load Processing in the Data Warehouse
403(44)
Tactical versus Strategic
404(2)
Operational Data Stores and Data Warehouses
405(1)
Post-Load Processing
406(6)
Summarization and Aggregation
407(3)
Filtering
410(1)
Merging and Denormalization
411(1)
Oracle8i Materialized Views
412(27)
Snapshots and Materialized Views
414(6)
Guidelines for Creating Materialized Views
420(5)
Refresh
425(7)
Query Rewrite
432(7)
Life before Materialized Views
439(7)
Parallel CREATE TABLE...AS SELECT (pCTAS)
440(1)
Parallel DML
440(2)
Procedural Code for Complex Logic
442(4)
Summary
446(1)
Administering and Monitoring the Oracle Data Warehouse
447(88)
Who Are We? Why Are We Here?
449(5)
Business Requirements
450(2)
System Requirements
452(2)
Administration Roles
454(2)
Data Warehouse Administrator (DWA)
454(1)
Database Administrator (DBA)
455(1)
System Administrator
455(1)
Security Administrator
455(1)
Network Administrator
455(1)
Help Desk and Problem Resolution
456(1)
Data Warehouse Security Issues
456(7)
Effective Security
457(1)
Unobtrusive Security
458(1)
Simple Security
458(2)
More Sophisticated Security Tools and Techniques
460(3)
Data Warehouse Configuration Issues
463(45)
Optimal Flexible Architecture (OFA)
463(4)
Build/Rebuild Documentation and Scripts
467(1)
Identifying All Inbound Data and Its Sources
468(1)
Job Scheduling
469(2)
Identifying All Outbound Data and Its Destinations
471(2)
Capacity Planning
473(14)
Development, Test, and Training Environments
487(3)
Backup and Recovery
490(15)
Enabling and Optimizing Auditing
505(3)
Configuration Rules of Thumb
508(6)
Locally Managed Tablespaces
508(1)
SYSTEM Tablespace
509(1)
TEMP Tablespace
510(1)
Rollback Segments and the RBS Tablespace
511(1)
DB_BLOCK_SIZE
512(1)
Setting ``init.ora'' Initialization Parameters
513(1)
Redo Logfiles
513(1)
Monitoring Rules of Thumb
514(19)
Monitoring the ``Worst'' SQL Statements
514(2)
Monitoring the Top Consumers in the Operating System
516(2)
Monitoring Configurable Resources
518(1)
Monitoring the AUDIT Session Statistics
518(1)
Monitoring Rollback Segment Contention
519(2)
Monitoring Latch Contention and Locking
521(5)
Monitoring the Number of Extents
526(2)
Monitoring Freespace
528(1)
Monitoring Tablespace Fragmentation
528(3)
Monitoring Invalid Compiled Objects
531(1)
Monitoring ``Stale'' Optimizer Statistics
532(1)
Summary
533(2)
Data Warehouse Performance Tuning
535(80)
Sizing Your System
537(1)
Setting Up the I/O Subsystem
538(7)
Pretuning the Data Warehouse
545(4)
Materialized Views
546(2)
Usage Tracking
548(1)
Optimizing the Load Phase
549(13)
SQL*Loader Direct Path versus ``Conventional SQL''
550(5)
Parallel Loads
555(1)
Drop and Re-Create Indexes for Large-Volume Loads
556(1)
Constraints in the Data Warehouse
557(1)
Pipelining Instead of ``Staging'' Flat Files
558(2)
Range Partitioning to publish New Data
560(1)
Range Partitioning to archive or purge Old Data
561(1)
Tuning the SQL
562(27)
Understanding SQL Tracing
563(10)
Formatting a SQL Tuning Report with TKPROF
573(16)
Looking for Problems
589(25)
Reactive Tuning
590(24)
Summary
614(1)
Parallel Execution in the Oracle Data Warehouse
615(62)
What Is Oracle Parallel Execution?
615(2)
When Are Parallel Operations Useful?
617(2)
Basic Parallel Queries
619(24)
Dynamic Partitioning
622(9)
Performing the Partitioning
631(2)
Recruiting Parallel Query Slaves
633(6)
Assigning Subranges
639(2)
Receiving and Collating the Results
641(2)
Parallel Direct-Path SQL*Loader
643(3)
Parallel Create Index (pCI)
646(3)
Parallel Recovery
649(2)
Parallel Create Table...As Select (pCTAS)
651(9)
Extent Trimming in Oracle
660(5)
Parallel Indexed Queries on Partitioned Tables
665(1)
Parallel DML
666(6)
Parallel Insert Append
667(1)
Parallel Insert Noappend
668(3)
Parallel Update and Delete
671(1)
Parallel DDL
672(3)
Parallel Alter Index...Rebuild
672(1)
Parallel Move Partition
673(1)
Parallel Split Partition
673(1)
Parallel Merge Partition
673(1)
Parallel Alter Table...Move
674(1)
Parallel Alter Table...Move Online
674(1)
Parallel Alter Index...Rebuild Partition
674(1)
Parallel Alter Index...Rebuild Subpartition
674(1)
Parallel Alter Index...Rebuild Online
674(1)
Parallel Statistics Gathering
675(1)
Summary
675(2)
Warehousing with Oracle Parallel Server
677(74)
What Is Oracle Parallel Server?
678(3)
Parallel Server versus Distributed Databases
681(1)
Again, What Is Parallel Server?
682(12)
File Cabinets and File Clerks
683(5)
Extending the Analogy to Include Parallel Server
688(6)
Why Use Parallel Server?
694(11)
Benefit: Performance and Capacity Scaleup
694(1)
Benefit: Higher Availability
695(3)
Benefit: Performance Speedup
698(1)
Cost: Greater Management Complexity
699(2)
Cost: Not Transparent to Some Applications
701(4)
Load Segregation Using Data Routing
705(3)
Randomly Segregating Load
708(3)
More on PCM Locks
711(3)
Administering Parallel Server
714(3)
Installing and Linking in the Parallel Server Option (Required)
714(1)
Separate Threads of Redo Log Files (Required)
715(2)
Optimizing Parallel Server for Data Warehouses
717(33)
Initialization Parameters
717(8)
Detecting Contention Specific to Parallel Server
725(7)
Reducing Enqueue Processing by Disabling ``Table Locks''
732(3)
Reducing Enqueue Processing with Locally Managed Tablespaces
735(2)
Optimizing Redo Log Files in Parallel Server
737(2)
Optimizing Rollback Segments in Parallel Server
739(2)
Optimizing Sorting in Parallel Server
741(2)
Optimizing Data Loading in Parallel Server
743(7)
Summary
750(1)
Distributing the Oracle Data Warehouse
751(44)
Reasons to Consider a Distributed Data Warehouse
752(4)
Increased Availability
752(1)
Cost
753(3)
Distributed Database Terminology
756(7)
Remote Query
757(1)
Distributed Query
757(2)
Remote Transaction
759(1)
Distributed Transaction
760(1)
Replication
761(2)
Oracle Distributed Database Technology
763(14)
Database Links
763(2)
Synonyms
765(1)
Views
765(2)
Gateways or ``Heterogenous Services''
767(1)
Oracle Replication Facilities
768(9)
Alternative Distributed Architectures
777(17)
Data Marts
777(2)
Distributed Data Warehouse
779(11)
Replicated Data Warehouse
790(4)
Summary
794(1)
Analytical Processing in the Oracle Data Warehouse
795(70)
Overview and Agenda
796(1)
Analytical Functions in Oracle SQL
797(9)
ROLLUP Extension to GROUP BY
798(2)
CUBE Extension to GROUP BY
800(1)
GROUPING Function
800(2)
Analytical Functions
802(4)
Browsing and Ad Hoc Reporting
806(3)
Oracle Discoverer
807(2)
Online Analytical Processing
809(1)
The Origins of OLAP
809(3)
The Express Data Model
812(5)
Dimensions
812(2)
Attributes
814(1)
Measures
814(3)
The History of Oracle Express
817(1)
The Technology of Oracle Express
818(4)
Data Storage and Access
819(1)
Offset Addressing
820(1)
Persistent Selections
820(1)
Inversions
820(1)
XCA Communications
820(2)
Application Architectures
822(7)
Data Distribution Architectures
822(3)
Load and Storage Strategy
825(4)
Implementing an OLAP Solution
829(17)
Requirements Definition
829(3)
Analysis
832(3)
Design and Build
835(11)
Data Loading and Integration with External Systems
846(8)
Loading Data
847(2)
Aggregation/Rollup
849(5)
Performance Issues and Tuning
854(5)
Gathering Information to Help Tune
855(3)
Express NT Server Configuration Manager Settings
858(1)
Front-End Alternatives
859(5)
The Oracle Express Applications
860(1)
Relational Access Manager/Administrator
861(1)
Oracle Express Objects and Express Analyzer
862(1)
Other Development Tools
863(1)
Express and the Web
863(1)
Summary
864(1)
Index 865

An electronic version of this book is available through VitalSource.

This book is viewable on PC, Mac, iPhone, iPad, iPod Touch, and most smartphones.

By purchasing, you will be able to view this book online, as well as download it, for the chosen number of days.

Digital License

You are licensing a digital product for a set duration. Durations are set forth in the product description, with "Lifetime" typically meaning five (5) years of online access and permanent download to a supported device. All licenses are non-transferable.

More details can be found here.

A downloadable version of this book is available through the eCampus Reader or compatible Adobe readers.

Applications are available on iOS, Android, PC, Mac, and Windows Mobile platforms.

Please view the compatibility matrix prior to purchase.