Oracle Database 11g: Administer a Data Warehouse

What you will learn:

Audience:

• Data Warehouse Administrator
• Data Warehouse Developer
• Application Developers
• Support Engineer
• Database Administrators
• Technical Consultant

Prerequisites:

Required Prerequisites:

• Ability to read and understand execution plans
• Good working knowledge of SQL and in data warehouse design and implementation
• Data Warehouse design, implementation, and maintenance experience

Course Objectives:

• Use parallel operations to reduce response time for data-intensive operations
• Extract, Transform, and Load data in the data warehouse
• Create, use, and refresh materialized views to improve the data warehouse performance
• Use Query rewrite to quickly answer business queries using materialized views
• Use SQL Access Advisor and PL/SQL procedures to tune materialized views for fast refresh and query rewrite
• Use the features of compression and resumable sessions
• Review the basic Oracle data warehousing concepts

Course Topics:

Introduction

• Development Tools
• Oracle SQL Developer
• Enterprise Manager
• Sample Schemas used

Data Warehouse Design: Overview

• Characteristics of a Data Warehouse
• Comparing OLTP and Data Warehouses
• Data Warehouse Architectures
• Data Warehouse Design
• Data Warehouse objects
• Data Warehouse Schemas

Data Warehouse Tuning Considerations

• Optimizing Star Queries
• Introducing Bitmap Join Indexes
• Understanding Star Query Optimization and Bitmap Joined Index Optimization

Partitioning Basics

• Partitioned Tables and Indexes
• Partitioning Methods
• Partitioning Types
• Partition Pruning and Star queries

Parallelism Concepts

• Operations That Can Be Parallelized
• How Parallel Execution Works
• Degree of Parallelism
• Parallel execution plan
• Automatice Parallelism

Parallel Operations in Data Warehouses

• Parallel Query
• Parallel DDL
• Parallel DML
• Tuning Parameters for Parallel Execution
• Balancing the Workload

ETL: Extraction and Transportation

• Extraction Methods
• Capturing Data With Change Data Capture
• Sources and Modes of Change Data Capture
• Publish and Subscribe Model: The Publisher and the Subscriber
• Synchronous and Asynchronous CDC
• Asynchronous AutoLog Mode and Asynchronous HotLog Mode
• Transportation in a Data Warehouse
• Transportable Tablespaces

ETL: Loading

• Loading Mechanisms
• Applications of External Tables
• Defining external tables with SQL*Loader
• Populating external tables with Data Pump
• Other Loading Methods

ETL: Transformation

• Data transformation
• Transformation Mechanisms
• Transformation Using SQL
• Table Functions
• DML error logging

Materialized Views

• The Need for Summary Management
• Types of Materialized Views
• Using Materialized Views for Summary Management
• Materialized View Dictionary views

Refreshing Materialized Views

• Refresh Options
• Refresh Modes
• Conditions That Effect Possibility of Fast Refresh
• Materialized View Logs
• Partition Change Tracking (PCT) Refresh
• Refresh Performance Improvements

Working With Dimensions

• What Are Dimensions
• Creating Dimensions and Hierarchies
• Dimensions and Privileges
• Dimension Restrictions
• Verifying Relationships in a Dimension
• Dimension Invalidation

Query Rewrite

• Query Rewrite: Overview
• What Can be Rewritten
• Conditions Required for Oracle to Rewrite a Query
• Query Rewrite guidelines
• Setting Initialization Parameters for Query Rewrite
• Query Rewrite Methods
• Partition Change Tracking (PCT) and Query Rewrite
• Query Rewrite Enhancement to Support Queries Containing Inline Views

Using the SQL Access Advisor, Compression, and Resumable Sessions

• SQL Access Advisor: Usage Model
• Setting Initial Options
• Specifying the Workload Source
• Recommendation Options
• Schedule and Review
• PL/SQL Procedure Flow
• Tuning Materialized Views for Fast Refresh and Query Rewrite
• Table Compression and Resumable Sessions

Suggested Next Courses:

• Oracle Database 11g: OLAP Essentials
• Oracle Database 11g: Performance Tuning DBA Release 2
• Implement Oracle Data Warehousing Best Practices NEW