Oracle Database 11g: Data Warehouse

Pre-Requisites: Ability to read and understand execution plans. Good working knowledge of SQL and in data warehouse design and implementation.

Key Features

Online live classroom available
Quality learning materials
Small Class Sizes
State of the Art Facility
Free Retakes
Instructor Led Classroom training
Certified Industry Experienced Teachers
100% Job Placement assistance

Oracle Workforce Development Program

What you will learn

You will 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 and use SQL Access Advisor and PL/SQL procedures to tune materialized views for fast refresh and query rewrite.

Benefits

Participants learn about Oracle’s Database partitioning architecture and how to identify the benefits of partitioning in addition to using parallel operations to reduce response time for data-intensive operations. Participants extract, transform, and load data into an Oracle database warehouse. Participants also use materialized views to improve the data warehouse performance and learn how query rewrites can improve performance.

Course content

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

Course Number : ORCL-7-0101

Duration : 40 hours

[]

1 Step 1

Request Information!

First Nameyour first name

Last Nameyour last name

Address

Phone Number

Emaila valid email

City

Commentsmore details

0 /

Success Stories

Real testimonials from our Students!

Global IT Changed My Life I remember the day I attended Global Information Technology. I met with the administrator who gave a lot of good information. My name is Larry Todd Sr. and I like to tell you about Global IT. Global IT really changed my life. If you are serious about starting a career, [...] Read more

Larry Todd

After being laid-off and getting frustrated looking for work in the current market with outdated AS/400 and Mainframe skills, I decided to visit a few training providers to investigate some tech training to upgrade my skills set. The third one I visited was Global Information Technology. My first impression was very positive. The representative’s passionate [...] Read more

David Hamel

I worked 17 yrs in manufacturing as a Hilo driver, a material processor and 9 yrs as a Quality assurance technician and was let go due to the economy in February 2007. During that time I’d always had a passion for the computing arena so after weighing my options and overcoming the fear that can [...] Read more

Ralph Cook

Through this letter I want to thank Mr. Manish Modi and Global information Technology ( GIT) for helping me restart my career. I was working in the IT field and took a break for family reasons. After being a full time mom for 11 years, I recently decided to restart my career and wanted to [...] Read more

Sonal Shirodkar

I’d just like to thank you and Global Information Technology for getting my career back on the right track. I had been unemployed for nearly 2 years after being laid off from ACCESS and I have been trying to find the right job. Couple months after applying to the unemployment, Michigan Works office approved me [...] Read more

Racha Ramdan