Contents

Title and Copyright Information

Send Us Your Comments

Preface

Audience

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

Integrated

Nonvolatile

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

Dimensions

Part III Physical Design

3 Overview of Physical Design

Moving from Logical to Physical Design

Physical Design

Physical Design Structures

Tablespaces

Partitions

Indexes

Constraints

4 Hardware and I/O

Striping

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

Terminology

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

Naming

Storage Characteristics

Build Methods

Used for Query Rewrite

Query Rewrite Restrictions

Refresh Options

ORDER BY

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

Timestamps

Partitioning

Triggers

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

Pivoting

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

Schemas

Star Schemas

Optimizing Star Queries

Tuning Star Queries

Star Transformation

17 SQL for Analysis

Overview

Analyzing Across Multiple Dimensions

Optimized Performance

A Scenario

Syntax

Details

Example

Interpreting NULLs in Results

Partial Rollup

Calculating Subtotals without ROLLUP

When to Use ROLLUP

Syntax

Details

Example

Partial Cube

Calculating Subtotals without CUBE

When to Use CUBE

Syntax

Examples

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

Constraints

Dimensions

Outer Joins

SQL Text Match

Aggregates

Grouping Conditions

Expression Matching

Date Folding

Statistics

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