Oracle Training : Oracle9i Data Warehouse Administration

Oracle9i Data Warehouse Administration

Oracle Training Overview

This course considers how to build, implement, tune and utilize data warehouses with Oracle technology. Logical data warehouse concepts are considered such as dimension tables, fact tables and star schemas. Implementing such logical concepts using the Oracle database is then presented including defining dimensions, hierarchies, measures and other objects. Physical implementation techniques are considered such as bitmap indexes, partitioned tables, materialized views, and others. Emphasis is placed on the parallel execution features of the database and how these can yield significant performance advantages.

This course was formerly called �Building Oracle9i Data Warehouses�.

Oracle Training Prerequisites

The course Introduction to Oracle9i: SQL
The course Introduction to Oracle9i: PL/SQL Language
The course Introduction to Oracle9i: Advanced SQL

Additional Recommended Prerequisites

In addition, the following courses are strongly recommended, although not mandatory, prerequisites:

The course Oracle 9i Architecture For Developers
The course Oracle 9i New & Advanced Features for Developers
The course Oracle 9i SQL Tuning OR
The course Oracle 9i Database: Administration � DBA I
The course Oracle 9i New & Advanced Features for DBAs
The course Oracle 9i SQL Tuning

Oracle Training Audience

Target audience for this course is database administrators, data warehouse administrators and application developers who will be responsible for implementing and using data warehouse technology.

Suggested Next Courses

The course Oracle 9i Discoverer For Power Users

Oracle Training Course Objectives

The objective of this course is to consider present a comprehensive consideration of data warehouse features which exist within the Oracle database. Major subject areas to be explored are:

Understanding star schemas and other data warehouse objects.
Understanding and encouraging optimization of star queries.
Creating and maintaining materialized views to enhance ad-hoc query performance.
Creating and maintaining dimensions to enhance ad-hoc query performance.
Performing dimensional analysis of data warehouse information.
Using the Summary Advisor tool for data warehouse design recommendations

Oracle Training Course duration

5 Days

Oracle Training Course outline

ABOUT DATA WAREHOUSING

UNDERSTANDING WAREHOUSE CONCEPTS & TERMS
CONTRAST OLTP & WAREHOUSE DATABASES

USING MATERIALIZED VIEWS

ENABLE MATERIALIZED VIEWS & QUERY REWRITE
CREATE THE MATERIALIZED VIEW

MAINTAINING MATERIALIZED VIEWS

MAINTENANCE OPTIONS
ABOUT THE TYPES OF VIEWS
ALTERING AND DROPPING VIEWS
DATA DICTIONARY STORAGE

REFRESHING MATERIALIZED VIEWS

SPECIFYING THE DEFAULT REFRESH OPTIONS
PERFORMING A REFRESH ON DEMAND
IMPLEMENTING FAST REFRESH

MONITOR QUERY REWRITE WITH EXPLAIN PLAN

GENERATING THE EXECUTION PLAN
VIEWING THE EXECUTION PLAN
INTERPRETING THE EXECUTION PLAN

CONTROLLING THE QUERY REWRITE FACILITY

QUERY REWRITE OPTIMIZER HINTS
UTILIZING CONSTRAINTS WITH QUERY REWRITE
QUERY REWRITE INTEGRITY LEVELS
QUERY REWRITE INFLUENCES

DIMENSIONS

CREATING & MAINTAINING DIMENSIONS
DATA DICTIONARY STORAGE
DIMENSION SYSTEM-SUPPLIED PACKAGES

THE SUMMARY ADVISOR TOOL

THE DBMS_OLAP() PACKAGE
INCORPORATING WORKLOAD STATISTICS
OEM SUMMARY ADVISOR WIZARD

DIMENSIONAL ANALYSIS OF DATA

DATA SAMPLING TECHNIQUES
AGGREGATION TECHNIQUES
BUILDING THE DATA WAREHOUSE CUBE

AN INTRODUCTION TO THE ANALYTIC FUNCTIONS

RANKING FUNCTIONS
UNDERSTANDING FUNCTION EXECUTION

INCORPORATING BITMAP INDEXES

STAR QUERIES & THE OPTIMIZER

A STAR TRANSFORMATION SCENARIO
ENCOURAGING STAR TRANSFORMATION

ETT FEATURES (EXTERNAL TABLES)

CREATING & ACCESSING EXTERNAL TABLES
PERFORMANCE CONSIDERATIONS
VIEWING & ALTERING PROPERTIES OF EXTERNAL TABLES

ETT FEATURES (TABLE FUNCTIONS)

IMPLEMENTING A PIPELINED TABLE FUNCTION