Course Code: 5640

Oracle Database 12c: SQL Workshop III - Advanced

Class Dates:
1/1/0001
12/2/2021
Length:
2 Days
Cost:
$1295.00
Class Time:
Technology:
Database
Delivery:

Overview

  • 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
  • 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

Prerequisites

  • 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 Disclaimer

*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 Details

  • 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