Oracle 10g Advanced SQL Programming Course

Topics covered on the 3 day Oracle 10g Advanced SQL Programming course

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
• Oracle's 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