Oracle Database 11g: Administer a Data Warehouse




    4 Day(s) Course


    Official Courseware


    Exam Voucher


    Certified Trainer


    1:1 Training Available on Demand


    Customize Your Batch



    The Oracle Database 11g: Administer a Data Warehouse training course helps participants in understanding the basic functionalities and features of administering a data warehouse. This training will also familiarize participants to deploy the various features of Oracle Database to improve performance and manageability in a data warehouse.

    Completion of this training course will enable candidates in:

    • Implementing partitioning.
    • Using parallel operations to reduce response time.
    • Extracting, transforming and loading data.
    • Creating, using and refreshing materialized views.
    • Using query rewrite to quickly answer business queries using materialized views.
    • Using SQL access advisor and PL/SQL procedures to tune materialized views.
    • Identifying the benefits of partitioning, to reduce response time for data-intensive operations.

    Course Benefits:

    Training of this course will help individuals to extract, transform and load data into an Oracle database warehouse. This course will also engage participants in using materialized views in order to improve data warehouse performance and also learn how query rewrites can improve performance.

    1. Introduction

    • Oracle SQL Developer
    • Types of Development Tools
    • Overview of Enterprise Manager

    2. Data Warehouse Design: Overview

    • Data Warehouse Characteristics
    • Compare OLTP and Data Warehouses
    • Data Warehouse: Architectures, Design, Object & Schemas

    3. Data Warehouse Tuning Considerations

    • Optimize Star Queries
    • Introduction to Bitmap Join Indexes
    • Bitmap Joined Index Optimization
    • Star Query Optimization

    4. Partitioning Basics

    • Partitioned Tables and Indexes
    • Partitioning Types
    • Partition Pruning and Star queries
    • Partitioning Methods

    5. Parallelism Concepts

    • Operations That Can Be Parallelized
    • Parallel Execution Working
    • Degree of Parallelism
    • Automatic Parallelism
    • Parallel execution plan

    6. Parallel Operations in Data Warehouses

    • Parallel Query
    • Balancing the Workload
    • Parallel DDL & Parallel DML
    • Tuning Parameters for Parallel Execution

    7. ETL: Extraction and Transportation

    • Extraction Methods
    • Capture Data with Change Data Capture
    • Sources and Modes of Change Data Capture
    • Publish and Subscribe Model
    • Synchronous and Asynchronous CDC
    • Asynchronous AutoLog Mode and HotLog Mode
    • Transportable Tablespaces
    • Transportation in a Data Warehouse

    8. ETL: Loading

    • Loading Mechanisms
    • Applications of External Tables
    • Define external tables with SQL*Loader
    • Populating external tables with Data Pump

    9. ETL: Transformation

    • Data transformation
    • Transformation Mechanisms
    • Transformation Using SQL
    • DML error logging
    • Table Functions

    10. Materialized Views

    • Need for Summary Management
    • Types of Materialized Views
    • Materialized View Dictionary views
    • Use Materialized Views for Summary Management

    11. 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

    12. Working with Dimensions

    • Define Dimensions
    • Create Dimensions and Hierarchies
    • Dimensions and Privileges
    • Dimension Restrictions
    • Dimension Invalidation
    • Verify Relationships in a Dimension

    13. Query Rewrite

    • Overview of Query Rewrite
    • What Can Be Rewritten?
    • Conditions Required for Oracle to Rewrite a Query
    • Guidelines for Query Rewrite
    • Set Initialization Parameters for Query Rewrite
    • Query Rewrite Methods
    • Partition Change Tracking (PCT) and Query Rewrite

    14. Using the SQL Access Advisor, Compression, and Resumable Sessions

    • Usage Model of SQL Access Advisor
    • Set Initial Options
    • Specify the Workload Source
    • Recommendation Options
    • Schedule and Review
    • PL/SQL Procedure Flow
    • Table Compression and Resumable Sessions
    • Tune Materialized Views for Fast Refresh and Query Rewrite

    The Oracle Database 11g Administer a Data Warehouse Course is ideal for:

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

    • Working knowledge of SQL and in data warehouse design and implementation
    • Knowledge & experience of Data Warehouse designing, implementation, and maintenance
    • Ability to read and understand execution plans

    Name Code Fee



    What Our Trainees Say

    Mercury Solutions Ltd. is rated 4.6 stars by based on 18 reviews.