Versión para impresora

Código: D70064GC10
Duración: 24 horas(4 días)
Horario:  9:00 a 15:00 horas 

Fechas previstas
Curso bajo demanda

Descripción del Curso

En este curso, los alumnos estudiarán la arquitectura de particiones de la base de datos de Oracle y los beneficios del particionado. También utilizarán operaciones paralelas para reducir el tiempo de respuesta de operaciones de grandes cantidades de datos. Los estudiantes aprenderán a extraer, transformar y cargar datos en un data warehouse de la base de datos Oracle.

Prerequisitos

• Good working knowledge of SQL and in data warehouse design and implementation
• Good working knowledge of SQL
• Good working knowledge of data warehouse design
• Ability to read and understand execution plans

 

 Objetivos

• 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

Contenido

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