Oracle Database 12c: SQL Workshop III - Advanced
CLASS DATE(s):
Request a Class

COURSE LENGTH: 2 Days

COURSE COST: $1295.00

COURSE TIMES: 9:00am - 4:30pm

Printable version of this course
print

COURSE OVERVIEW

There is a difference between using the SQL language in the classroom or in a prototype environment and successfully deploying it as part of a production application. This textbook goes beyond the basics of the SQL language and considers topics that must be addressed in a real-world environment.

Certification This textbook and the others within this series consider subjects applicable to certification as an Oracle Database Certified SQL Expert. The topics considered are included within "Exam 1Z0-047: Oracle Database: SQL Certified Expert".

AUDIENCE AND PREREQUISITES

Target Audience

The target audience for this textbook is all Oracle professionals, both business and systems professionals. Among the specific groups for whom this textbook will be helpful are:
Application designers and database developers
ETL, BI and data analytics developers
Database and data warehouse administrators
Web server administrators

ORACLE DATABASE 12C: SQL FUNDAMENTALS (LEVELS I & II) is a prerequisite to this one. Note further that in order to complete all of the workshop exercises, one will need support from an Oracle 12c database administrator versed in the topics considered in ORACLE DATABASE 12C: ADMINISTRATION WORKSHOP II ADVANCED ADMINISTRATION.

  

*Course Cost listed does not include the cost of courseware. Please refer to your Invoice for the additional cost. If you have any questions, please contact us (learn@vtec.org or 207-775-0244). Course subject to minimum enrollment. Courses maybe run as a LDL - Live Distance Learning if minimum enrolled is not met.

COURSE TOPICS:


Objectives
In a continuous integration (CI) or continuous deployment (CD) application environment, how does one properly handle disruptive schema changes using the editioning feature?
What best practices should SQL developers employ to achieve optimum performance?
What advanced table types and index types should be included in the schema design to gain performance benefits.
How can complex data scenarios be handled, such as the processing of hierarchies, multimedia object types and others?

Advanced Schema Design: Oracle SecureFile & LOBs
LOB Concepts
LOB Internal Mechanisms
BasicFile LOBs
Table DDL With LOBs
Controlling LOB Physical Storage
Initialize Internal LOBs
Initialize External LOBs
LOB Columns & SQL
SecureFile LOBs
Monitoring SecureFile Performance
Migrating BasicFile To SecureFile

Advanced Schema Design: Clusters & IOTs
Index Clusters
Using Index Clusters
Hash Clusters
Index-Organized Tables
Defining Index-Organized Tables

Advanced Schema Design: Advanced Table Definitions
Temporary Tables
Read-Only Tables
Column Default Values
Virtual Columns
Unused Columns
Invisible Columns
Validating Schema Objects
GUID Unique Identifiers

High Performance Applications: Advanced Index Usage
B-tree Index Internals
Indexes & Performance
Rebuild & Coalesce Indexes
Creating Very Large Indexes
Data Dictionary Metadata
Function-Based Indexes
Reverse-Key Indexes
Bitmap Indexes
Bitmap Join Indexes
Linguistic Indexes
Invisible Indexes

High Performance Applications: Database Result Cache
About Database Memory & Caching
About The Result Cache
Configure The Result Cache
Results From The Result Cache
Caching SQL Results
Caching PL/SQL Function Results
Managing & Monitoring The Cache
Managing The Cache With DBMS_RESULT_CACHE()
Monitoring The Cache With The System Views

High Performance Applications: Advanced Transaction Management
Flashback Drop
Examining The Recycle Bin
FLASHBACK TABLE...TO BEFORE DROP
Purging The Recycle Bin
Asynchronous COMMIT Options
Influencing Locking Behavior

Advanced Applications: Processing Hierarchies
Why We Need Hierarchies
Processing 1:N Hierarchies
Oracle-Specific Form
ANSI/ISO Standard SQL Form
Processing M:N Hierarchies
Bill-of-materials (Explosion & Implosion)
More Explosion Examples
More Implosion Examples
Advanced Hierarchy Processing

Advanced Applications: Edition-Based Redefinition
Why Online Redefinition?
Edition-Based Architecture
Implementing Editions
Using Editions

Advanced Applications: Redefinition With Cross-Edition Triggers
Redefinition Theoretical Challenges
Forward Cross-Edition Triggers
Reverse Cross-Edition Triggers
Post-Upgrade Task Checklist