Top 20 Features in Oracle Database 23c for DBAs - Part-1

Oracle Database 23c, also known as “23ai”, brings a host of new features and enhancements that cater to database administrators (DBAs). These features improve performance, security, management, and ease of use. I am still reading more on all the new features that this new release has brought and in my previous article, I talked about Ubiquitous Database Search in Oracle 23c. In this 04-part article, I will talk about the below top 20 features (in my opinion) in Oracle 23c, for DBAs.

• Part-1
  • JSON Relational Duality
  • Automatic Indexing Enhancements
  • Blockchain Tables
  • Native JSON Data Type
  • SQL Macro
• Part-2
  • Oracle Machine Learning Enhancements
  • In-Memory Column Store Enhancements
  • Automatic SQL Plan Management (SPM)
  • High-Frequency Statistics Collection
  • Automatic Index Optimization
• Part-3
  • Multilingual Engine (MLE)
  • Enhanced Data Masking and Redaction
  • Automatic Data Optimization (ADO) Enhancements
  • Graph Studio Enhancements
  • Partitioned Hybrid Tables
• Part-4
  • Blockchain Ledger Table Enhancements
  • Active Data Guard DML Redirection
  • Sharding Enhancements
  • Automatic Workload Repository (AWR) Enhancements
  • SQL Quarantine

1. JSON Relational Duality

JSON Relational Duality enables seamless coexistence and interchangeability between JSON and relational data models. JSON Relational Duality allows developers and DBAs to define tables that can be accessed and manipulated using both JSON and SQL. This duality provides the flexibility to work with the best aspects of each data model. JSON is excellent for hierarchical, document-based data structures, while relational models are superior for structured, tabular data and complex queries.

Key Benefits

1. Flexibility: Developers can choose the best data model for their application needs without being locked into one approach.
2. Performance: Optimized access paths ensure high performance for both JSON and relational queries.
3. Simplicity: Reduces the need for complex transformations between JSON and relational data, simplifying data management and application development.
4. Interoperability: Facilitates seamless integration of applications that prefer different data models.

Creating a Duality Table

To create a table that supports JSON Relational Duality, you can use the JSON data type for one or more columns. Here’s an example of creating such a table:

CREATE TABLE employees (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    data JSON
);

Inserting JSON Data

You can insert JSON data into the table as follows:

INSERT INTO employees (data) VALUES ('{"name": "John Doe", "role": "Developer"}');

Querying JSON Data

You can query JSON data using JSON syntax within SQL statements. Oracle provides JSON-specific functions and operators to facilitate this:

SELECT data.name, data.role FROM employees;

This query extracts the name and role attributes from the JSON documents stored in the data column.

Manipulating JSON Data

You can also update JSON data directly:

UPDATE employees
SET data = JSON_TRANSFORM(data, SET '$.role' = 'Senior Developer')
WHERE data.name = 'John Doe';

Relational Views on JSON Data

Oracle Database 23c allows you to define relational views over JSON data, providing a relational perspective on hierarchical JSON structures:

CREATE VIEW employee_view AS
SELECT
    id,
    data.name AS name,
    data.role AS role
FROM
    employees;

You can then query this view like any other relational table:

SELECT * FROM employee_view;

Indexing JSON Data

To optimize performance, you can create indexes on JSON data. Oracle supports JSON-specific indexing mechanisms:

CREATE INDEX idx_employee_name ON employees (data.name);

This index improves the performance of queries that filter on the name attribute within the JSON data.

Combining JSON and Relational Data

One of the powerful aspects of JSON Relational Duality is the ability to combine JSON and relational data in queries. For example, you can join a relational table with a table containing JSON data:

SELECT e.data.name, d.department_name
FROM employees e
JOIN departments d ON e.data.department_id = d.id;

Optimized Storage: JSON data is stored efficiently to minimize storage overhead and maximize performance.

Automatic Data Type Recognition: Oracle can automatically recognize and convert data types between JSON and relational formats.

Enhanced JSON Functions: New and enhanced JSON functions provide more powerful and flexible ways to manipulate JSON data.

2. Automatic Indexing Enhancements

Enhancements in automatic indexing ensure that the database automatically tunes itself by creating and managing indexes based on the workload, without requiring much manual intervention from DBAs.

Key Enhancements:

• Improved Index Selection Algorithms: Enhanced algorithms analyze the workload more effectively to determine the most beneficial indexes, reducing the risk of unnecessary or redundant indexes.
• Automatic Index Validation: New mechanisms validate the effectiveness of automatically created indexes before making them visible to queries, ensuring they genuinely improve performance.
• Periodic Re-Evaluation: The system periodically re-evaluates the effectiveness of indexes to adapt to changing workloads and remove or modify indexes that are no longer beneficial.
• Better Integration with Database Workload: Integration with workload capture and replay features ensures that indexes are optimized based on realistic usage patterns.
• Enhanced Reporting and Monitoring: Improved reporting features provide detailed insights into automatic indexing activities, making it easier for DBAs to monitor and understand the impact of automatic indexing.

Automatic Indexing works by continuously monitoring the SQL workload, identifying candidates for indexing, and automatically creating, validating, and managing these indexes. DBAs can still configure, manage, and monitor automatic indexing on the database. Below are a few examples:

--Enable Automatic Indexing:
ALTER SYSTEM SET AUTO_INDEXING = TRUE;

--Check Automatic Indexing Status:
SELECT parameter_name, parameter_value
FROM DBA_AUTO_INDEX_CONFIG
WHERE parameter_name = 'AUTO_INDEXING';

--Review Automatic Index Activity:
SELECT * FROM DBA_AUTO_INDEX_EXECUTIONS;

--Review Proposed Indexes:
SELECT * FROM DBA_AUTO_INDEX_IMPLEMENTATIONS
WHERE STATUS = 'PROPOSED';

--Monitor Index Validations:
SELECT * FROM DBA_AUTO_INDEX_VALIDATIONS;

--Review Index Usage:
SELECT INDEX_NAME, TABLE_NAME, TABLE_OWNER, LAST_USED
FROM DBA_INDEXES
WHERE AUTO = 'YES';

--Disable Automatic Indexing:
ALTER SYSTEM SET AUTO_INDEXING = FALSE;

--Configure Automatic Indexing Parameters:
BEGIN
DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE', 'IMPLEMENT'); -- Options: 'IMPLEMENT', 'REPORT ONLY'
END;

BEGIN
DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_IDLE_INDEXES', 30); -- Retention period in days
END;

BEGIN
DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SPACE_BUDGET', 50); -- Space budget as a percentage of total space
END;

Monitoring and Reporting:

A DBA can get detailed insights through dynamic performance views and reports. For example,

#Automatic Indexing Summary:
SELECT * FROM DBA_AUTO_INDEX_SUMMARY;

#Detailed Execution Log:
SELECT * FROM DBA_AUTO_INDEX_EXECUTIONS;

#Index Impact Analysis:
SELECT INDEX_NAME, TABLE_NAME, IMPACT, STATUS
FROM DBA_AUTO_INDEX_IMPLEMENTATIONS;

3. Blockchain Tables

Blockchain Tables in Oracle Database 23c provide immutable, tamper-evident storage for sensitive data. This feature is designed to ensure data integrity and security by preventing any modifications to the data once it is inserted. Blockchain tables work by maintaining a cryptographic hash chain of the rows. Each row contains a hash of the previous row’s data, creating a continuous chain. This makes it impossible to alter any row without breaking the chain and making the tampering evident.

-- Creating a Blockchain Table
CREATE BLOCKCHAIN TABLE tbl_bc_transactions (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    transaction_date DATE,
    details VARCHAR2(255)
) NO DROP UNTIL 30 DAYS IDLE -- The table cannot be dropped until it has been idle for at least 30 days.
NO DELETE UNTIL 365 DAYS AFTER INSERT; -- Rows cannot be deleted until 365 days after their insertion.

-- Inserting data into the blockchain table
INSERT INTO transactions (transaction_date, details) 
VALUES (SYSDATE, 'Payment of $100');

-- Querying blockchain table data
SELECT * FROM tbl_bc_transactions;

If necessary, you can easily extend the retention periods for blockchain tables and their rows. For example,

ALTER BLOCKCHAIN TABLE transactions 
NO DROP UNTIL 60 DAYS IDLE;

ALTER BLOCKCHAIN TABLE transactions 
NO DELETE UNTIL 730 DAYS AFTER INSERT;

The “DBA_BLOCKCHAIN_TABLES” view can be queried to view the properties and status of the blockchain tables.

SELECT table_name, retention_days, idle_time
FROM DBA_BLOCKCHAIN_TABLES
WHERE table_name = 'TRANSACTIONS';

4. Native JSON Data Type

The native JSON data type provides a highly efficient and optimized way to store and process JSON documents. This new data type offers several advantages over previous methods of storing JSON data in Oracle databases, such as using VARCHAR2 or CLOB data types.

JSON documents are stored in a binary format. It allows for flexible schema designs, supporting both structured and semi-structured data. Operations on JSON data, such as querying, indexing, and updates, are faster compared to using traditional data types.

-- Creating a table with a JSON column
CREATE TABLE employees (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    data JSON
);

-- Inserting JSON data
INSERT INTO employees (data) VALUES ('{"name": "John Doe", "role": "Developer", "department": "IT"}');

-- Querying JSON data
SELECT data.name, data.role 
FROM employees;

-- Using JSON Functions:
SELECT JSON_VALUE(data, '$.name') AS name,
       JSON_VALUE(data, '$.role') AS role
FROM employees;

-- Updating JSON Data:
UPDATE employees
SET data = JSON_TRANSFORM(data, SET '$.role' = 'Senior Developer')
WHERE JSON_VALUE(data, '$.name') = 'John Doe';

-- Indexing JSON Data:
--Functional Indexes:
CREATE INDEX idx_employee_name ON employees (JSON_VALUE(data, '$.name'));
--Path-based Indexes:
CREATE INDEX idx_employee_data ON employees (data) INDEXTYPE IS JSON;

Advanced JSON Functions

-- JSON_VALUE: Extracts a scalar value from a JSON document.
SELECT JSON_VALUE(data, '$.name') 
FROM employees;

-- JSON_QUERY: Extracts an object or array from a JSON document.
SELECT JSON_QUERY(data, '$.address') 
FROM employees;

-- JSON_TABLE: Converts JSON data into a relational format
SELECT * 
FROM JSON_TABLE(
    (SELECT data FROM employees),
    '$' COLUMNS (
        name VARCHAR2(50) PATH '$.name',
        role VARCHAR2(50) PATH '$.role'
    )
);

-- JSON_TRANSFORM: Modifies JSON data by adding, updating, or removing elements
UPDATE employees
SET data = JSON_TRANSFORM(data, REMOVE '$.department')
WHERE JSON_VALUE(data, '$.name') = 'John Doe';

5. SQL Macro

A SQL Macro is a named SQL expression that can be invoked in SQL statements, similar to functions, but with significant differences in terms of execution and performance. SQL Macros are expanded at parse time, meaning they are replaced with their definitions during query compilation, resulting in efficient execution.

Key Characteristics:

1. Reusable SQL Expressions:
   • Encapsulate complex SQL logic into reusable macros to simplify and standardize queries.
2. Parse-Time Expansion:
   • SQL Macros are expanded at parse time, not at runtime, ensuring optimized performance.
3. Parameterization:
   • SQL Macros can accept parameters, making them flexible and adaptable to different use cases.
4. Enhanced Readability:
   • Improve code readability by abstracting repetitive and complex SQL logic into named macros.

Usage:

-- Creating a Scalar macro
CREATE OR REPLACE FUNCTION get_employee_salary (p_employee_id NUMBER)
RETURN NUMBER SQL_MACRO IS
BEGIN
  RETURN 'SELECT salary FROM employees WHERE employee_id = p_employee_id';
END get_employee_salary;

-- Using the scalar macro in a query
SELECT employee_id, get_employee_salary(employee_id) AS salary
FROM employees
WHERE department_id = 10;

-- Creating a table macro
CREATE OR REPLACE FUNCTION get_department_employees (p_department_id NUMBER)
RETURN VARCHAR2 SQL_MACRO IS
BEGIN
  RETURN 'SELECT * FROM employees WHERE department_id = p_department_id';
END get_department_employees;

-- Using the table macro in a query
SELECT * 
FROM get_department_employees(20);

SQL Macros can accept multiple parameters to handle various use cases. For example,

CREATE OR REPLACE FUNCTION get_employee_info (p_employee_id NUMBER, p_include_salary BOOLEAN)
RETURN VARCHAR2 SQL_MACRO IS
BEGIN
  RETURN CASE 
           WHEN p_include_salary THEN 'SELECT employee_id, name, salary FROM employees WHERE employee_id = p_employee_id'
           ELSE 'SELECT employee_id, name FROM employees WHERE employee_id = p_employee_id'
         END;
END get_employee_info;

It can also include conditional logic to adapt its behavior based on input parameters.

CREATE OR REPLACE FUNCTION get_filtered_employees (p_department_id NUMBER, p_min_salary NUMBER)
RETURN VARCHAR2 SQL_MACRO IS
BEGIN
  RETURN 'SELECT * FROM employees WHERE department_id = p_department_id AND salary >= p_min_salary';
END get_filtered_employees;

Benefits of SQL Macros

1. Performance Optimization:
   • Since SQL Macros are expanded at parse time, they do not incur the runtime overhead associated with regular functions. This results in optimized query performance.
2. Code Reusability:
   • Encapsulating SQL logic into macros promotes reusability, reduces code duplication, and simplifies maintenance.
3. Improved Readability:
   • SQL Macros help in abstracting complex SQL expressions, making the overall query more readable and easier to understand.
4. Flexibility:
   • Parameterized SQL Macros offer flexibility, allowing the same macro to be used in different scenarios with different parameters.

That is all for first part of this article. Hope it helps. I will touchbase on the following in part-2:

• Oracle Machine Learning Enhancements
• In-Memory Column Store Enhancements
• Automatic SQL Plan Management (SPM)
• High-Frequency Statistics Collection
• Automatic Index Optimization