Oracle Database 11g: Administer a Data Warehouse NEW

What you will learn

Students learn about Oracle’s Database partitioning architecture and identify the benefits of partitioning. Students also use parallel operations to reduce response time for data-intensive operations. Participants extract, transform, and load data into an Oracle database warehouse. Students also use materialized views to improve the data warehouse performance. Students also learn how query rewrite can improve a query’s performance. Students use the SQL Access Advisor to optimize the entire workload. Finally, students learn how to tune materialized views for fast refresh and query rewrite and how to use the compression and resumable sessions features.

Learn to:
  • Implement partitioning
  • Use parallel operations to reduce response time
  • Extract, Transform, and Load data
  • 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


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

Prerequisites
 Good working knowledge of data warehouse design
Ability to read and understand execution plans
Good working knowledge of SQL and in data warehouse design and implementation
Good working knowledge of SQL
Oracle 10g: Data Warehousing Fundamentals
Oracle Database 11g: Administration Workshop I
Oracle Database 11g: Performance Tuning NEW

Course Objectives
 Review the basic Oracle data warehousing concepts
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

Course Topics
Introduction
 Development Tools
Oracle SQL Developer
Enterprise Manager
Sample Schemas used

Data Warehouse Design: Overview
 What is a Data Warehouse?
Characteristics of a Data Warehouse
Comparing OLTP and Data Warehouses
Data Warehouse Architectures
Data Warehouse Design
Data Warehouse objects
Data Warehouse Schemas
Star Transformation

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

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 Warehouse Builder 10g: Implementation Part II
Oracle Warehouse Builder 10g: Implementation Part I
Oracle Database 11g: Analytic SQL for Data Warehousing NEW