Advanced SQL Oracle 11g Programming – ORA520
The three day Oracle 11g Advanced SQL Programming course give students the opportunity to develop a deeper understanding into relational database design and RDBMS operation. This course specifically addresses all the Oracle 11g Advanced SQL features.Oracle 11g Advanced SQL Programming – ORA520 – 3 Days Request a Class Date
The 3 Day Advanced SQL Oracle 11g Programming course give students the opportunity to develop a deeper understanding into relational database design and RDBMS operation.
This course covers concepts and specific Oracle 11g SQL syntax for extended Oracle datatypes. Additionally students learn analysis and tuning techniques to increase SQL performance, and master advanced features of Oracle 11g SQL for large data sets and data warehouses.
Course ID: ORA520 Duration: 3 days
Audience: Application developers, database administrators, system administrators and users who write applications and procedures that access an Oracle 11g database.
Learn Advanced Oracle 11g SQL Programming From These 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
- 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