Oracle SQL Programming
and Tuning
Also see our
Oracle SQL Tuning Training Class
and
Advanced SQL training
An intensive Oracle SQL training
course
? 2007-2016 by Burleson Corporation
* Understand
Oracle.
* Explore Oracle data
access.
* Learn DDL and
DML.
* Use PL/SQL.
* Understand Oracle Report
writing.
|
|
Introduction to SQL and PL/SQL is an intensive course that is designed to give the student
maximum exposure to Oracle SQL. The student learns by doing,
and this class has dozens of in-class exercises and the student
will be guided from very simple SQL commands to increasingly
complex PL/SQL coding techniques.
The topics start with the basics of SQL
and progress into increasingly complex queries, including table
joins, subqueries and creating Oracle views. The PL/SQL
section begins with simple concepts and the student gradually
masters PL/SQL through increasingly challenging classroom
exercises.
This course is designed for practicing Oracle professionals who
have basic experience with SQL and the use of a relational
database. Prior experience with Oracle is not required, but
experience using SQL with a relational database is highly
desirable.
|
This course was designed by Donald K.
Burleson, an acknowledged leader in Oracle SQL tuning.
Burleson was chosen by Oracle Press to write the authorized edition
of Oracle High-Performance SQL tuning. Burleson Corporation instructors offer decades of
real world DBA experience in Oracle features, and they will share
their Oracle secrets in this intense Oracle SQL tuning
training
Learning Objectives
By the end of
this course the student will be able to formulate advanced SQL queries including
correlated subqueries and outer joins. The student will also become familiar
with the internals of Oracle SQL and will be able to use the EXPLAIN PLAN utility to
tune SQL statements.
Oracle SQL Programming &
Tuning
Syllabus
(c)
2007-2016 by Donald K.
Burleson
Day 1
– Introduction and overview of Oracle
This is an introduction to Oracle relational database and the Oracle
architecture. We discuss Oracle data storage architecture, Oracle table and
indexes, and database access methods.
1-1: The Oracle Relational Database
• The relational model
• Oracle overview
• One-to-many relationships
• Many-to-many relationships
• Recursive many-to-many relationships
1-2: Inside the Oracle Architecture
• SGA regions
• Background processes
• Disk architectures
1-3: Data storage
• Disk storage
• Solid state disks
• RAM caches
• Flat-file data storage (BFILE, external tables)
• Spreadsheet file access
1-4: Introduction to Oracle Tables
• Standard tables
• Index organized tables
• External tables
1-5: Introduction to Oracle Indexes
• B-tree indexes
• Bitmap indexes
• Bitmap join indexes
• Star indexes
1-6: Database access methods
• SQL*Plus
• SQL Worksheet
• Procedural programs
• ODBC
• SQL*XL
Day 2 – Introduction to Oracle Data Access
This is an introduction to Oracle SQL and SQL*Plus.
2-1: SQL Processing
• Syntax checking
• Semantic processing
• SQL parsing
• Creating the executable
• Inside the library cache
2-2: Oracle data dictionary
• The X$ fixed tables
• The DBA Views
• The v$ views
• Running dictionary scripts
2-3: SQL*Plus
• Entering SQL*Plus
• Using the desc command
• Using the spool command
2-4: Syntax of SQL
• Select, project and join
• WHERE clause
• ORDER BY clause
• GROUP BY clause
• In-line views
• SQL in SELECT clause (Oracle9i new feature)
2-5: Report generation with SQL*Plus
• Entering SQL*Plus
• Running an SQL command
• Building and formatting an SQL command
• Formatting a SQL report
• Spooling a report
2-6: Views
• Logical encapsulation of complex queries
• Using the DBA_VIEWS view
• Problems combining views
Day 3 – Introduction to DDL and DML
This is an introduction to Oracle schema definition and data
updating.
3-1: SQL schema navigation
• Schema entity types
• Relationships between schema entities
3-2: Schema components
• Data files
• Tablespaces
• Oracle objects (tables, indexes, IOT, etc.)
3-3: Referential Integrity
• Implementing Data Integrity
• Types of RI – not null, unique check, foreign key
• Rules for using RI
• System-named constraints (SYSnnn)
• Viewing Constraint Information with dba_constraints
3-4: DDL Introduction
• Create a database
• Create a table
• Create an index
• Altering schema objects
3-5: DML Introduction
• Inserts
• Updates
• Deletes
• RI constraints (restrict, cascade)
• Snapshot too old
3-6: Read consistency, locking & DML
• Why read consistency?
• UNDO segments and read consistency
Day 4 – PL/SQL
4-1: Introduction to the PL/SQL Language
• Interpreted language
• Syntax examples
• Procedures, packages and functions
4-2: PL/SQL Language structures
• Looping within PL/SQL
• If testing (if then else syntax)
• Exception conditions
• Array processing
4-3: SQL with PL/SQL
• Using a cursor
• Fetching from a cursor
4-4: PL/SQL Packages and Procedures
4-5: Extending SQL with a user-defined PL/SQL function
4-6: Oracle supplied DBMS packages
Day 5 – Oracle Report Writing
This is an introduction to Oracle reporting tools and techniques.
Your will learn how to use the SQL*Plus formatting commands to
create easy reports from Oracle, how to join multiple tables
together and how to easily aggregate, summarize and analyze Oracle
table data. You also learn how Oracle access the tables and indexes
and see how to ensure that your SQL uses the fastest methods for
accessing your data.
5-1: Formatting commands in SQL*Plus
5-2: Analytical functions in SQL
5-3: Table joins
• Equi-join
• Outer join
• Hiding joins by creating views
• Using IN, NOT IN, EXISTS and NOT EXISTS
• Subqueries
• Exercise – write a subquery
• Correlated subquery
• Non-correlated subqueries
5-4: Subqueries in SQL
• IN and NOT In operators
• Sub-queries
• EXISTS clause
5-5: Aggregation in SQL
• Between operator
• Using wildcards in queries (LIKE operator)
• Count(*)
• Sum
• Avg
• Min and max
• Using the group by clause
5-6: Materialized Views for reports
This is a BC Oracle SQL training
course (c) 2002-2016
We also provide PLSQL optimization and PL/SQL optimization consulting
services.
|
