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.
Oracle 10g Advanced SQL Programming – ORA507 Request a Class Date
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.
Learn Oracle 10g Advanced SQL Programming:
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
