Oracle SQL / PL SQL
Intro to the class
History of SQL
Evolution of SQL
Exercise – declarative SQL
Review of pubs database
SQL constructs:
Select
Project
Join
Describing Oracle tables
Exercise – investigate the pubs database
Restricting row returns
Where clause
Exercise – write a sample query in the pubs database
Complex Boolean logic in SQL
ROWID restrictions
Entity/relation modeling
Types of data relationships
Data normalization
One-to-many relationships
One-to-many relationships
Many-to-many relationships
Recursive many-to-many relationships
Optimizing Oracle SQL
Steps in SQL optimization
Parsing a SQL statement
First_rows vs all_rows optimization
Oracle optimizer overview
Management issues with system-wide optimization
Different modes of SQL optimization
Bi-modal databases
Rule-based optimization
Cost-based optimization
All rows optimization
First_rows optimization
Exercise – display and change optimizer_mode
SQL Semantic Analysis
Generating the execution plan
Using optimizer plan stability
Using the v$sql view
Using the v$sql_plan view
Exercise – Query the library cache
Using SQL*Plus
Creating basic reports
Exercise – format a simple query in the pubs database
Using the set commands
Adjusting line output
Setting page size and line size
Echo
Feedback
Verify
term out
Exercise – set the SQL*plus environment
Column wrapping
Exercise – add column statements
Creating breaks and summaries
Exercise – add breaks and summaries
Adding prompts to queries
Exercise – parameterize a SQL*Plus script
Explain plan and reading execution plans
Explain plan
Oracle auto trace
How to read an execution plan
Altering SQL execution plans
Using hints
Table joining internals
Sort-merge joins
Nested Loop joins
Hash joins
STAR joins
Bitmap joins
Exercise – Change table join techniques & evaluate performance
Equi -join ( Exercise – write an equi -join)
Outer join ( Exercise – write an outer join)
Hiding joins by creating views (Exercise – create a view of a join)
Sub queries
Using IN, NOT IN, EXISTS and NOT EXISTS
Sub queries
Exercise – write a sub query
Correlated sub query
Non-correlated sub queries
Advanced SQL operators
Between operator
IN and NOT In operators
Sub-queries
EXISTS clause
Using wild cards in queries (LIKE operator)
SQL access methods
Review of Basic joining methods
Merge join
Hash Join
Nested Loop join
Advanced SQL operators
Between operator
DML and SQL Tuning
Writing and optimizing INSERT statements
Writing and optimizing DELETE statements
Writing and optimizing UPDATE statements
Optimizer Statistics
Purpose of statistics
Types of statistics (table, column, system)
Histogram statistics
Dynamic sampling
using dbms_stats
|
Exporting/importing statistics
Statistics management
Exercise – gather system stats
Optimizer Statistics
Purpose of statistics
Types of statistics (table, column, system)
Histogram statistics
Dynamic sampling
using dbms_stats
Exporting/importing statistics
Statistics management
Exercise – gather system stats
SQL Tuning and full-table scans
Basics of file I/O
Sequential reads vs. scattered reads
When full scans are best
RAM caching in the SGA
Automating table caching
Solid State Disks
Tracking full-scans over time with AWR
Exercise – Query v$sql
Aggregation IN sql
Aggregation in SQL
Count(*)
Sum
Avg
Min and max
Using the group by clause
PL/SQL Section
Basics of PL/SQL
PL/SQL architecture
PL/SQL and SQL*Plus
PL/SQL Basics
Introduction to PL/SQL
PL/SQL as a 4thgeneration language
Compiling vs. Interpreting
Declare statement
Exercise: Write “hello” world PL/SQL program
Using PL/SQL Variables PL/SQL Constants PL/SQL Data types Error messages – user_errors and show errors PL/SQL wrapper utility
PL/SQL structures
Simple blocks
Control structures
PL/SQL records
Recognizing the Basic PL/SQL Block and Its Sections
Describing the Significance of Variables in PL/SQL
Distinguishing Between PL/SQL and Non-PL/SQL Variables
Declaring Variables and Constants
Executing a PL/SQL Block
Error checking – exception handling
Defining exceptions
Using the when others clause
Ensuring complete error checking
Passing error messages to calling routine
Boolean logic in PL/SQL
Identifying the Uses and Types of Control Structures
Constructing an IF Statement
Constructing and Identifying Different Loop Statements
Controlling Block Flow Using Nested Loops and Labels
Using Logic Tables
If-then-else structure
Testing for numbers characters and Booleans
Cursors in PL/SQL
Cursor basics
Using a cursor for a multi-row SQL query
Iteration in PL/SQL
For loop
While loop
PL/SQL tables
Defining PL/SQL tables
Reasons to use PL/SQL tables
Populating a PL/SQL table
Retrieving from a PL/SQL table
Dynamic SQL in PL/SQL
Introduction to the dbms_sql package
Creating a dynamic SQL statement
Nested blocks in PL/SQL
Creating nested blocks
Understanding scope in nested blocks
Triggers in PL/SQL
Triggers and database events
Defining a trigger
Timing a trigger
Enabling and disabling a trigger
Stored procedures, functions and packages
Basics of stored procedures
Basics of functions
Basics of packages
Defining stored procedures & functions
Function and stored procedures prototypes
Passing arguments to functions and stored procedures
Recompiling functions and stored procedures
Pinning packages in the SGA with dbms_shared_pool.keep
Package forward declaration
Package dependency
Package overloading
Listing package information
Bulking in PL/SQL
Bulk queries
Bulk DML (for all statement)
Using cursor attributes
Analyzing impact of bilk operations
|
