Oracle9i Data Warehousing Guide -- Contents

What is a Data Warehouse?

Subject Oriented

Integrated

Nonvolatile

Time Variant

Contrasting OLTP and Data Warehousing Environments

Data Warehouse Architectures

Data Warehouse Architecture (Basic)

Data Warehouse Architecture (with a Staging Area)

Data Warehouse Architecture (with a Staging Area and Data Marts)

Logical versus Physical Design in Data Warehouses

Creating a Logical Design

Data Warehousing Schemas

Star Schemas

Other Schemas

Data Warehousing Objects

Typical Example of Data Warehousing Objects and Their Relationships

Moving from Logical to Physical Design

Physical Design

Physical Design Structures

Tablespaces

Tables and Partitioned Tables

Views

Integrity Constraints

Indexes and Partitioned Indexes

Materialized Views

Dimensions

Overview of Hardware and I/O Considerations in Data Warehouses

Why Stripe the Data?

Automatic Striping

Manual Striping

Local and Global Striping

Analyzing Striping

RAID Configurations

RAID 0 (Striping)

RAID 1 (Mirroring)

RAID 0+1 (Striping and Mirroring)

Striping, Mirroring, and Media Recovery

RAID 5

The Importance of Specific Analysis

Overview of Parallel Execution

When to Implement Parallel Execution

Granules of Parallelism

Block Range Granules

Partition Granules

Partitioning Design Considerations

Types of Partitioning

Local Indexes Versus Global Indexes

Why Integrity Constraints are Useful in a Data Warehouse

Overview of Constraint States

Typical Data Warehouse Integrity Constraints

UNIQUE Constraints in a Data Warehouse

FOREIGN KEY Constraints in a Data Warehouse

RELY Constraints

Integrity Constraints and Parallelism

Integrity Constraints and Partitioning

View Constraints

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 Aggregates

Materialized Views Containing Only Joins

Nested Materialized Views

Creating Materialized Views

Naming

Storage Characteristics

Build Methods

Enabling Query Rewrite

Query Rewrite Restrictions

Refresh Options

ORDER BY Clause

Materialized View Logs

Using Oracle Enterprise Manager

Using Materialized Views with NLS Parameters

Registering Existing Materialized Views

Partitioning and Materialized Views

Partition Change Tracking

Partitioning a Materialized View

Partitioning a Prebuilt Table

Rolling Materialized Views

Choosing Indexes for Materialized Views

Invalidating Materialized Views

Security Issues with Materialized Views

Altering Materialized Views

Dropping Materialized Views

Analyzing Materialized View Capabilities

Using the DBMS_MVIEW.EXPLAIN_MVIEW Procedure

MV_CAPABILITIES_TABLE.CAPABILITY_NAME Details

MV_CAPABILITIES_TABLE Column Details

Overview of Materialized View Management Tasks

What are Dimensions?

Creating Dimensions

Multiple Hierarchies

Using Normalized Dimension Tables

Dimension Wizard

Viewing Dimensions

Using The DEMO_DIM Package

Using Oracle Enterprise Manager

Using Dimensions with Constraints

Validating Dimensions

Altering Dimensions

Deleting Dimensions

Overview of ETL

ETL Tools

Daily Operations

Evolution of the Data Warehouse

Overview of Extraction in Data Warehouses

Understanding Extraction Methods in Data Warehouses

Logical Extraction Methods

Physical Extraction Methods

Change Data Capture

Data Warehousing Extraction Examples

Extraction Using Data Files

Extraction Via Distributed Operations

Overview of Transportation in Data Warehouses

Understanding Transportation Mechanisms in Data Warehouses

Transportation Using Flat Files

Transportation Through Distributed Operations

Transportation Using Transportable Tablespaces

Overview of Loading and Transformation in Data Warehouses

Transformation Flow

Loading Mechanisms

SQL*Loader

External Tables

OCI and Direct-path APIs

Export/Import

Transformation Mechanisms

Transformation Using SQL

Transformation Using PL/SQL

Transformation Using Table Functions

Loading and Transformation Scenarios

Parallel Load Scenario

Key Lookup Scenario

Exception Handling Scenario

Pivoting Scenarios

Using Partitioning to Improve Data Warehouse Refresh

Optimizing DML Operations During Refresh

Implementing an Efficient Merge

Maintaining Referential Integrity

Purging Data

Refreshing Materialized Views

Complete Refresh

Fast Refresh

ON COMMIT Refresh

Manual Refresh Using the DBMS_MVIEW Package

Refresh Specific Materialized Views with REFRESH

Refresh All Materialized Views with REFRESH_ALL_MVIEWS

Refresh Dependent Materialized Views with REFRESH_DEPENDENT

Using Job Queues for Refresh

When Refresh is Possible

Recommended Initialization Parameters for Parallelism

Monitoring a Refresh

Checking the Status of a Materialized View

Tips for Refreshing Materialized Views with Aggregates

Tips for Refreshing Materialized Views Without Aggregates

Tips for Refreshing Nested Materialized Views

Tips After Refreshing Materialized Views

Using Materialized Views With Partitioned Tables

Fast Refresh with Partition Change Tracking

Fast Refresh with CONSIDER FRESH

About Oracle Change Data Capture

Publish and Subscribe Model

Example of a Change Data Capture System

Components and Terminology for Synchronous Change Data Capture

Installation and Implementation

Security

Columns in a Change Table

Views

Synchronous Mode of Data Capture

Publishing Change Data

Subscribing to Change Data

Steps Required to Subscribe to Change Data

What Happens to Subscriptions When the Publisher Makes Changes

Export and Import Considerations

Overview of the Summary Advisor in the DBMS_OLAP Package

Summary Advisor Wizard

Using the Summary Advisor

Identifier Numbers

Workload Management

Loading a User-Defined Workload

Loading a Trace Workload

Loading a SQL Cache Workload

Validating a Workload

Removing a Workload

Using Filters with the Summary Advisor

Removing a Filter

Recommending Materialized Views

SQL Script Generation

Summary Data Report

When Recommendations are no Longer Required

Stopping the Recommendation Process

Sample Sessions

Estimating Materialized View Size

ESTIMATE_MVIEW_SIZE Parameters

Is a Materialized View Being Used?

DBMS_OLAP.EVALUATE_MVIEW_STRATEGY Procedure

Schemas in Data Warehouses

Star Schemas

Optimizing Star Queries

Tuning Star Queries

Using Star Transformation

Overview of SQL for Aggregation in Data Warehouses

Analyzing Across Multiple Dimensions

Optimized Performance

An Aggregate Scenario

Interpreting NULLs in Examples

ROLLUP Extension to GROUP BY

When to Use ROLLUP

ROLLUP Syntax

Partial Rollup

CUBE Extension to GROUP BY

When to Use CUBE

CUBE Syntax

Partial CUBE

Calculating Subtotals without CUBE

GROUPING SETS Expression

Composite Columns

Concatenated Groupings

Concatenated Groupings and Hierarchical Data Cubes

Considerations when Using Aggregation

Hierarchy Handling in ROLLUP and CUBE

Column Capacity in ROLLUP and CUBE

HAVING Clause Used with GROUP BY Extensions

ORDER BY Clause Used with GROUP BY Extensions

Using Other Aggregate Functions with ROLLUP and CUBE

Computation Using the WITH Clause

Overview of SQL for Analysis in Data Warehouses

Ranking Functions

Windowing Aggregate Functions

Treatment of NULLs as Input to Window Functions

Windowing Functions with Logical Offset

Cumulative Aggregate Function

Moving Aggregate Function

Centered Aggregate Function

Windowing Aggregate Functions with Logical Offsets

Variable Sized Window

Windowing Aggregate Functions with Physical Offsets

FIRST_VALUE and LAST_VALUE

Reporting Aggregate Functions

Reporting Aggregate Example

RATIO_TO_REPORT

LAG/LEAD Functions

LAG/LEAD Syntax

FIRST/LAST Functions

FIRST/LAST Syntax

FIRST/LAST As Regular Aggregates

FIRST/LAST As Reporting Aggregates

Linear Regression Functions

REGR_COUNT

REGR_AVGY and REGR_AVGX

REGR_SLOPE and REGR_INTERCEPT

REGR_R2

REGR_SXX, REGR_SYY, and REGR_SXY

Linear Regression Statistics Examples

Sample Linear Regression Calculation

Inverse Percentile Functions

Normal Aggregate Syntax

Inverse Percentile Restrictions

Hypothetical Rank and Distribution Functions

Hypothetical Rank and Distribution Syntax

WIDTH_BUCKET Function

WIDTH_BUCKET Syntax

User-Defined Aggregate Functions

CASE Expressions

Creating Histograms with User-defined Buckets

OLAP

Benefits of OLAP and RDBMS Integration

Data Mining

Enabling Data Mining Applications

Predictions and Insights

Mining Within the Database Architecture

Java API

Introduction to Parallel Execution Tuning

When to Implement Parallel Execution

Operations That Can Be Parallelized

The Parallel Execution Server Pool

How Parallel Execution Servers Communicate

Parallelizing SQL Statements

Types of Parallelism

Parallel Query

Parallel DDL

Parallel DML

Parallel Execution of Functions

Other Types of Parallelism

Initializing and Tuning Parameters for Parallel Execution

Selecting Automated or Manual Tuning of Parallel Execution

Using Automatically Derived Parameter Settings

Setting the Degree of Parallelism

How Oracle Determines the Degree of Parallelism for Operations

Balancing the Workload

Parallelization Rules for SQL Statements

Enabling Parallelism for Tables and Queries

Degree of Parallelism and Adaptive Multiuser: How They Interact

Forcing Parallel Execution for a Session

Controlling Performance with the Degree of Parallelism

Tuning General Parameters for Parallel Execution

Parameters Establishing Resource Limits for Parallel Operations

Parameters Affecting Resource Consumption

Parameters Related to I/O

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 System Statistics

Monitoring Operating System Statistics

Affinity and Parallel Operations

Affinity and Parallel Queries

Affinity and Parallel DML

Miscellaneous Parallel Execution Tuning Tips

Formula for Memory, Users, and Parallel Execution Server Processes

Setting Buffer Pool Size for Parallel Operations

Balancing the Formula

Parallel Execution Space Management Issues

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

Overview of Query Rewrite

Cost-Based Rewrite

When Does Oracle Rewrite a Query?

Enabling Query Rewrite

Initialization Parameters for Query Rewrite

Controlling Query Rewrite

Privileges for Enabling Query Rewrite

Accuracy of Query Rewrite

How Oracle Rewrites Queries

Text Match Rewrite Methods

General Query Rewrite Methods

When are Constraints and Dimensions Needed?

Special Cases for Query Rewrite

Query Rewrite Using Partially Stale Materialized Views

Query Rewrite Using Complex Materialized Views

Query Rewrite Using Nested Materialized Views

Query Rewrite with CUBE, ROLLUP, and Grouping Sets

Did Query Rewrite Occur?

Explain Plan

DBMS_MVIEW.EXPLAIN_REWRITE Procedure

Design Considerations for Improving Query Rewrite Capabilities

Contents

Title and Copyright Information

Send Us Your Comments

Preface

Part I Concepts

1 Data Warehousing Concepts

Part II Logical Design

2 Logical Design in Data Warehouses

Part III Physical Design

3 Physical Design in Data Warehouses

4 Hardware and I/O Considerations in Data Warehouses

5 Parallelism and Partitioning in Data Warehouses

6 Indexes

7 Integrity Constraints

8 Materialized Views

9 Dimensions

Part IV Managing the Warehouse Environment

10 Overview of Extraction, Transformation, and Loading

11 Extraction in Data Warehouses

12 Transportation in Data Warehouses

13 Loading and Transformation

14 Maintaining the Data Warehouse

15 Change Data Capture

16 Summary Advisor

Part V Warehouse Performance

17 Schema Modeling Techniques

18 SQL for Aggregation in Data Warehouses

19 SQL for Analysis in Data Warehouses

20 Advanced Analytic Services

21 Using Parallel Execution

22 Query Rewrite

Part VI Miscellaneous

A Glossary

B Sample Data Warehousing Schema