Close

Tóm tắt khóa học:

This course will help you understand the basic concepts of administering a data warehouse. You'll learn to use various Oracle Database features to improve performance and manageability in a data warehouse

Thời lượng khóa học: 4 ngày


Nội dung khóa học:

This course will help you understand the basic concepts of administering a data warehouse. You'll learn to use various Oracle Database features to improve performance and manageability in a data warehouse.

Learn To:

  • Implement partitioning.
  • Use parallel operations to reduce response time.
  • Extract, transform and load data.
  • Create, use and refresh materialized views to improve 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 rewrites.
  • Identify the benefits of partitioning, in addition to using parallel operations to reduce response time for data-intensive operations.

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