Oracle SQL 26ai
6 Saturdays (9am to 11am) | Intermediate to Advanced | Course Notes
Course Content
Week 1 — SQL Foundations & AI Vector Search
- Review of core SQL concepts: SELECT, joins, subqueries, and set operations
- Introduction to Oracle AI Database 26ai SQL enhancements
- Understanding the native
VECTOR data type and its use in AI workloads
- Building vector indexes (HNSW) for high-performance similarity search
- Writing SQL queries with vector search:
VECTOR_DISTANCE and VECTOR_EMBEDDING functions
- Combining vector searches with traditional relational predicates, text, JSON, and graph queries
- Case study: Semantic search on unstructured data (documents, images)
- Performance considerations for vector queries with concurrent DML
Week 2 — JSON Relational Duality & New Data Types
- Introduction to JSON Relational Duality: transparently accessing data as JSON or relational tables
- Creating duality views with
CREATE JSON DUALITY VIEW
- Using SQL/JSON functions (
JSON_EXISTS, JSON_VALUE, JSON_QUERY) on duality views
- Transactional consistency and ACID guarantees with duality views
- Support for the
BOOLEAN data type in Oracle Machine Learning and SQL
- Using
BOOLEAN in SQL expressions and PL/SQL blocks
- Handling JSON documents with nested structures and arrays
- Performance tuning for JSON-based applications
Week 3 — Operational Property Graphs & Graph Queries
- Introduction to Property Graph model in Oracle Database
- Creating and populating property graphs using SQL and
CREATE PROPERTY GRAPH
- Querying graphs using the new SQL-based graph syntax (SQL/PGQ)
- Performing graph analytics: shortest path, connectivity, and community detection
- Combining graph queries with relational and vector searches
- Using graph views and materialized graph snapshots for performance
- Real-time graph analysis against operational data without ETL
- Integration with Oracle Graph Studio and visualization tools
Week 4 — SQL Firewall, Security & Data Use Case Domains
- Understanding the SQL Firewall: real-time protection against SQL injection and unauthorized queries
- Configuring SQL Firewall policies and whitelisting safe SQL patterns
- Monitoring and logging SQL Firewall events for compliance and forensic analysis
- Declaring Data Use Case Domains for columns (email, URL, password, etc.)
- Using domain metadata for automatic validation and code generation
- Implementing schema-level privileges for simplified access control
- Combining SQL Firewall with unified auditing for comprehensive security
- Best practices for securing SQL applications in the AI database
Week 5 — SQL Simplifications & Performance Tuning
- New SQL simplifications: time bucketing functions for time-series analysis
- Non-positional
INSERT syntax (using column names)
GROUP BY ALL for aggregated rollups without explicit grouping
- Using
EXCEPT, INTERSECT, and other set operators with the new enhancements
- Understanding optimizer statistics and execution plans in 26ai
- Performance tuning for AI Vector Search workloads — indexing strategies and cost-based optimizer
- Diagnosing slow queries using SQL Diagnostic Report (deep-level HTML reports with plan history, optimizer stats, and index details)
- Monitoring query performance with Telemetry Streaming (REST, SQL, PromQL)
Week 6 — Advanced PL/SQL, Automation & Capstone Project
- Writing PL/SQL procedures and functions for complex business logic
- Using dynamic SQL and native dynamic SQL for flexible applications
- Leveraging SQL Firewall and auditing in PL/SQL contexts
- Automating repetitive tasks using Oracle Scheduler and
DBMS_SCHEDULER
- Exporting and importing data using Data Pump (the original
EXP utility is desupported)
- Integrating SQL with REST endpoints and external APIs
- Understanding the deprecation of EM Express and using SQL Developer or OCI Database Management
- Capstone Project: Build a complete application using AI Vector Search, JSON Duality, and graph queries to solve a real-world business problem, with integrated security and performance optimization
Project: Design and implement a multi-modal SQL application combining vector search, JSON duality, and property graph queries for a unified data platform.
Starting Date: 4th July 2026
Course Fee: £2990