Oracle8i Data Warehousing Guide
Release 2 (8.1.6)







Title and Copyright Information

Send Us Your Comments


Knowledge Assumed of the Reader
Installation and Migration Information
Application Design Information
How Oracle8i Data Warehousing Guide Is Organized
Conventions Used in This Manual

Part I Concepts

1 Data Warehousing Concepts

What is a Data Warehouse?
Subject Oriented
Time Variant
Contrasting a Data Warehouse with an OLTP System
Typical Data Warehouse Architectures

Part II Logical Design

2 Overview of Logical Design

Logical vs. Physical
Create a Logical Design
Data Warehousing Schemas
Star Schemas
Other Schemas
Data Warehousing Objects
Fact Tables

Part III Physical Design

3 Overview of Physical Design

Moving from Logical to Physical Design
Physical Design
Physical Design Structures

4 Hardware and I/O

Input/Output Considerations
Staging File Systems

5 Parallelism and Partitioning

Overview of Parallel Execution Tuning
When to Implement Parallel Execution
Tuning Physical Database Layouts
Types of Parallelism
Partitioning Data
Partition Pruning
Partition-wise Joins

6 Indexes

Bitmap Indexes
B-tree Indexes
Local Versus Global

7 Constraints

Why Constraints are Useful in a Data Warehouse
Overview of Constraint States
Typical Data Warehouse Constraints
Unique Constraints in a Data Warehouse
Foreign Key Constraints in a Data Warehouse
RELY Constraints
Constraints and Parallelism
Constraints and Partitioning

8 Materialized Views

Overview of Data Warehousing with Materialized Views
Materialized Views for Data Warehouses
Materialized Views for Distributed Computing
Materialized Views for Mobile Computing
The Need for Materialized Views
Components of Summary Management
Schema Design Guidelines for Materialized Views
Types of Materialized Views
Materialized Views with Joins and Aggregates
Single-Table Aggregate Materialized Views
Materialized Views Containing Only Joins
Creating a Materialized View
Storage Characteristics
Build Methods
Used for Query Rewrite
Query Rewrite Restrictions
Refresh Options
Using Oracle Enterprise Manager
Nested Materialized Views
Why Use Nested Materialized Views?
Rules for Using Nested Materialized Views
Restrictions when Using Nested Materialized Views
Limitations of Nested Materialized Views
Example of a Nested Materialized View
Nesting Materialized Views with Joins and Aggregates
Nested Materialized View Usage Guidelines
Registration of an Existing Materialized View
Partitioning a Materialized View
Partitioning the Materialized View
Partitioning a Prebuilt Table
Indexing Selection for Materialized Views
Invalidating a Materialized View
Security Issues
Guidelines for Using Materialized Views in a Data Warehouse
Altering a Materialized View
Dropping a Materialized View
Overview of Materialized View Management Tasks

9 Dimensions

What is a Dimension?
Drilling Across
Creating a Dimension
Multiple Hierarchies
Using Normalized Dimension Tables
Dimension Wizard
Viewing Dimensions
Using The DEMO_DIM Package
Using Oracle Enterprise Manager
Dimensions and Constraints
Validating a Dimension
Altering a Dimension
Deleting a Dimension

Part IV Managing the Warehouse Environment

10 ETT Overview

ETT Overview
ETT Tools
ETT Sample Schema

11 Extraction

Overview of Extraction
Extracting Via Data Files
Extracting into Flat Files Using SQL*Plus
Extracting into Flat Files Using OCI or Pro*C Programs
Exporting into Oracle Export Files Using Oracle's EXP Utility
Copying to Another Oracle Database Using Transportable Tablespaces
Extracting Via Distributed Operations
Change Capture

12 Transportation

Transportation Overview
Transportation of Flat Files
Transportation Via Distributed Operations
Transportable Tablespaces

13 Transformation

Techniques for Data Transformation Inside the Database
Transformation Flow
Transformations Provided by SQL*Loader
Transformations Using SQL and PL/SQL
Data Substitution
Key Lookups
Emphasis on Transformation Techniques

14 Loading and Refreshing

Refreshing a Data Warehouse
Using Partitioning to Improve Data Warehouse Refresh
Populating Databases Using Parallel Load
Refreshing Materialized Views
Complete Refresh
Fast Refresh
Tips for Refreshing Using Refresh
Complex Materialized Views
Recommended Initialization Parameters for Parallelism
Monitoring a Refresh
Tips after Refreshing Materialized Views

15 Summary Advisor

Summary Advisor
Collecting Structural Statistics
Collection of Dynamic Workload Statistics
Recommending Materialized Views
Estimating Materialized View Size
Summary Advisor Wizard
Is a Materialized View Being Used?

Part V Warehouse Performance

16 Schemas

Star Schemas
Optimizing Star Queries
Tuning Star Queries
Star Transformation

17 SQL for Analysis

Analyzing Across Multiple Dimensions
Optimized Performance
A Scenario
Interpreting NULLs in Results
Partial Rollup
Calculating Subtotals without ROLLUP
When to Use ROLLUP
Partial Cube
Calculating Subtotals without CUBE
When to Use CUBE
When to Use GROUPING
Hierarchy Handling in ROLLUP and CUBE
Column Capacity in ROLLUP and CUBE
HAVING Clause Used with ROLLUP and CUBE
ORDER BY Clause Used with ROLLUP and CUBE
Analytic Functions
Ranking Functions
Windowing Functions
Reporting Functions
Lag/Lead Functions
Statistics Functions
Case Expressions
CASE Example
Creating Histograms with User-defined Buckets

18 Tuning Parallel Execution

Introduction to Parallel Execution Tuning
When to Implement Parallel Execution
Initializing and Tuning Parameters for Parallel Execution
Selecting Automated or Manual Tuning of Parallel Execution
Automatically Derived Parameter Settings under Fully Automated Parallel Execution
Setting the Degree of Parallelism and Enabling Adaptive Multi-User
Degree of Parallelism and Adaptive Multi-User and How They Interact
Enabling Parallelism for Tables and Queries
Forcing Parallel Execution for a Session
Controlling Performance with PARALLEL_THREADS_PER_CPU
Tuning General Parameters
Parameters Establishing Resource Limits for Parallel Operations
Parameters Affecting Resource Consumption
Parameters Related to I/O
Example Parameter Setting Scenarios for Parallel Execution
Example One: Small Datamart
Example Two: Medium-sized Data Warehouse
Example Three: Large Data Warehouse
Example Four: Very Large Data Warehouse
Miscellaneous Tuning Tips
Formula for Memory, Users, and Parallel Execution Server Processes
Setting Buffer Pool Size for Parallel Operations
Balancing the Formula
Examples: Balancing Memory, Users, and Parallel Execution Servers
Parallel Execution Space Management Issues
Tuning Parallel Execution on Oracle Parallel Server
Overriding the Default Degree of Parallelism
Rewriting SQL Statements
Creating and Populating Tables in Parallel
Creating Temporary Tablespaces for Parallel Sort and Hash Join
Executing Parallel SQL Statements
Using EXPLAIN PLAN to Show Parallel Operations Plans
Additional Considerations for Parallel DML
Creating Indexes in Parallel
Parallel DML Tips
Incremental Data Loading in Parallel
Using Hints with Cost-Based Optimization
Monitoring and Diagnosing Parallel Execution Performance
Is There Regression?
Is There a Plan Change?
Is There a Parallel Plan?
Is There a Serial Plan?
Is There Parallel Execution?
Is The Workload Evenly Distributed?
Monitoring Parallel Execution Performance with Dynamic Performance Views
Monitoring Session Statistics
Monitoring Operating System Statistics

19 Query Rewrite

Overview of Query Rewrite
Cost-Based Rewrite
Enabling Query Rewrite
Initialization Parameters for Query Rewrite
Privileges for Enabling Query Rewrite
When Does Oracle Rewrite a Query?
Query Rewrite Methods
SQL Text Match Rewrite Methods
General Query Rewrite Methods
Query Rewrite with CUBE/ROLLUP Operator
When are Constraints and Dimensions Needed?
Complex Materialized Views
View-based Materialized View
Rewrite with Nested Materialized Views
Expression Matching
Date Folding
Accuracy of Query Rewrite
Did Query Rewrite Occur?
Explain Plan
Controlling Query Rewrite
Guidelines for Using Query Rewrite
Outer Joins
SQL Text Match
Grouping Conditions
Expression Matching
Date Folding

Part VI Miscellaneous

20 Data Marts

What Is a Data Mart?
How Is It Different from a Data Warehouse?
Dependent, Independent, and Hybrid Data Marts
Extraction, Transformation, and Transportation

A Glossary

Copyright © 1999 Oracle Corporation.

All Rights Reserved.