Oracle 10g Advanced SQL Programming - ORA507

In this class, students will develop deeper insight into relational database design and RDBMS operation, learn concepts and specific SQL syntax for extended Oracle datatypes, learn analysis and tuning techniques to increase SQL performance, and master advanced features of Oracle SQL for large data sets and data warehouses.
Course ID: ORA507

Duration: 3 days

Audience: Application developers, database administrators, system administrators and users who write applications and procedures that access an Oracle 10g database.

Prerequisites: Oracle 10g SQL Programming.
Topics:

Database Design Concepts

• Relational Databases
• The Relational Model
• Relational Operations
• The Database Design Process
• Normalization
• Second and Third Normal Forms
• Other Normal Forms
• Applications for Relational Databases

SQL Subqueries

• Overview Of Subqueries
• Inline Views
• Correlated Subqueries
• EXISTS Clause vs. IN Clause
• Group Comparisons: ANY and ALL
• Scalar Subquery Expression
• Subqueries and DML Statements
• Subquery Factoring: The WITH Clause
• Top-N and Bottom-N analysis
• CREATE TABLE and Subqueries

Hierarchical Queries

• Hierarchical Data
• Hierarchical Terminology
• Hierarchical Query
• Hierarchical Pseudocolumns
• SYS_CONNECT_BY_PATH
• Processing Hierarchical Queries

Object Types

• Object-Oriented Programming
• The Oracle Object Relational Model
• Creating Object Types
• Querying Object Types
• DML with Object Types
• Object Methods
• Object Views
• VARRAYs
• Nested Tables

Times, Dates, and Strings

• Datetime Fields
• Dates and Timestamps
• Intervals
• Date and Interval Literals
• Date Arithmetic
• Date Functions
• Character Types
• Session and Database Parameters
• REGEXP Functions
• Regular Expressions Supported by REGEXP
• Applying REGEXP Functions

Temporary Tables

• Undo and Redo
• Temporary Tables Defined
• Data Lifetime - Transaction vs. Session
• Creating Temporary Tables
• Managing Temporary Tables
• Storage of Temporary Tables
• Effects of DML and TRUNCATE

SQL Tuning Tools

• Automated Statistics Gathering
• The DBMS_STATS Package
• SQL Tuning Advisor
• SQL Tuning Sets
• SQL Access Advisor
• Retrieving Execution Plans
• EXPLAIN PLAN
• Using DBMS_XPLAN
• Interpreting Explain Plan Results
• SQL Trace
• TKPROF

SQL Tuning

• Tuning Goals
• The Optimizer
• Optimizer Statistics
• Identifying SQL to Tune
• Optimizer Hints
• Optimizer Goal Hints
• Access Path Hints
• Join Hints
• Additional Hints
• Plan Stability
• Creating Stored Outlines

Indexes

• Indexes
• B-tree and Composite Indexes
• Reverse Key and Unique Indexes
• Function-Based Indexes
• Bitmap Indexes
• Index-Organized Tables
• Managing Indexes

Oracle Analytic Functions

• Analytic Functions
• OVER, PARTITION BY, and ORDER BY
• Windowing
• ROLLUP
• CUBE
• Grouping Sets
• RANK
• Modeling
• Model Clauses

Data Warehouse Features

• Partitioned Tables
• Partitioning Methods
• Partition Pruning and Partition-wise Joins
• Bitmap Indexes
• Materialized Views
• Creating Materialized Views
• Refreshing Materialized Views
• The MERGE Statement
• Multi-table INSERT Statements
• Parallel Statements

Formatting Reports with SQL*Plus

• Page Formatting
• Computations
• SQL*Plus Options for Formatting
• Saving the Output
• Data Extraction with SQL*Plus