Advanced SQL
Training
Inhouse
Description
-
Type
Training
-
Methodology
Inhouse
-
Duration
2 Days
The course covers new commands introduced with Oracle8i and Oracle9i. It looks at using Large Objects in the database, new Data Warehouse commands, extensions to partitioned tables and Oracle's object-oriented approach to data storage. The course also focuses on the performance tuning utilities available in the Oracle database and will enable the delegate to identify problems with their code. Suitable for: Systems developers, DBA's who need to know more advanced SQL and performance tuning techniques.
About this course
Attendees should have experience of Oracle databases and preferably experience of using SQL. Ideally delegates will have attended the Fundamentals of SQL and SQL*Plus course
Reviews
Course programme
Course Overview
This course is designed specifically for experienced SQL users who wish to use the advanced features of SQL and learn how to performance tune their queries.
Course ContentThe DECODE Command
- Producing Matrix Reports
- Flipping a Table
- Using MOD in DECODE
- Columns and Computations
- Force Row-by-Row Changes
Dynamic SQL
- SQL Methods
- Performing Dynamic SQL with DBMS_SQL Package
- Using client side dynamic SQL.
Partitioned Tables
- Range Partitions
- Hash Partitions
- Composite Partitions
- Indexing Partitions
- List Partitioning
- Maintenance of Partitions
Data Warehousing Improvements
- Unconditional INSERT
- Pivoting INSERT
- Conditional INSERT
- Conditional FIRST INSERT
- UPSERT Statement
- New Joins
- USING Clause
- ON Clause
- CASE Expressions
- GROUPING SETS Clause
Oracle Objects
- Overview of Object type,
- Object Tables, Object Method,
- Object Collections,
- Object Views,
- Object Operators.
Large Objects (LOBs)
- Using Large Objects (LOBs)
- LOBs, CLOBs, and BFILEs
- Datatypes and Storage
- Manipulating LOB Values
- Selecting LOB Values
- Inserting, Updating and Deleting LOBs
Tuning
- Joins
- Joins in General
- Un-indexed, Indexed and Outer Joins
- Writing Good SQL Statements
- Data Conversion
- LIKE, NOT, OR IN Operators
- ORDER BY and GROUP BY
- Query Path Ranking
- Sub Queries
- Indexes and MAX/MIN
SQL Trace
- The Trace Facility
- Enabling SQL Trace
- Analysing Trace output
TKPROF
- Sort Options
- SQL Trace Statistics
Explain Plan
- Creating the Plan Table
- Explain Plan Syntax
Advanced SQL