General Description
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.
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
The Sideris textbook 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 the Sideris textbook ORACLE DATABASE 12C: ADMINISTRATION WORKSHOP II – ADVANCED ADMINISTRATION.
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".
Content Summary
Volumes: 1
Pages: 478
Hands-on Workshops: 9
Exercises: 78
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?
Contents
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
