DBA Hub

📋Steps in this guide1/14

What’s New in Oracle 23ai? What are its Latest Features?

Discover the most powerful new features in Oracle 23ai, including JSON Relational Duality, Immutable Tables, Graph Queries, AI Indexing, and Passwordless Authentication.Learn how these innovations benefit DBAs and developers with practical examples and real-world insights.

oracle configurationintermediate
by OracleDba
15 views
1

1. JSON Relational Duality – The SQL vs. NoSQL Debate is Over!

If you’ve ever had to choose between a relational database (SQL) and a document database (NoSQL) , you know how frustrating it can be. Developers love JSON for flexibility , while DBAs love relational models for data integrity and query power . Oracle 23ai solves this problem! With JSON Relational Duality , you can now: - Store and access the same data as both JSON and relational format simultaneously. - Use SQL queries on JSON data without conversion overhead. - Eliminate the need for complex ETL processes between NoSQL and SQL databases.
2

Example Use Case – E-commerce Catalog

Imagine you’re working for Amazon . Your product catalog is stored in JSON for faster API responses , but your finance team needs SQL for generating revenue reports . With Oracle 23ai , you don’t need separate storage for these two formats anymore! Your backend APIs can read/write JSON , while your reporting team runs SQL queries on the same data .
3

Code Example: Insert JSON and Query It with SQL

Mind-blowing, right? You get NoSQL speed with SQL power , without needing a separate database.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Creating a table with JSON Relational Duality
CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    product_data JSON RELATIONAL DUALITY
);
-- Inserting JSON data
INSERT INTO products VALUES (1, 
    '{ "name": "iPhone 15", "price": 799, "stock": 50 }'
);

-- Querying JSON data using SQL
SELECT product_data.name, product_data.price
FROM products
WHERE product_data.stock > 10;
4

2. True Immutable Tables – Tamper-Proof Data Storage

Imagine a world where nobody can alter your data, not even DBAs . Sounds scary? Well, it’s actually super useful for industries like banking, healthcare, and legal compliance . With Oracle 23ai Immutable Tables , you get: - Tamper-proof records (Once written, data can’t be modified or deleted .) - Regulatory compliance (Great for audit logs, financial transactions, and patient records.) - Better security (Protects against accidental or malicious data changes.)
5

Example Use Case – Banking Transactions

A bank like HDFC processes millions of transactions daily. If someone modifies past transactions , it could lead to fraud. With Immutable Tables , transaction records are 100% tamper-proof , ensuring complete data integrity.
6

Code Example: Creating an Immutable Table

Immutable Tables ensure that once data is inserted, it stays forever!

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Creating an immutable table
CREATE IMMUTABLE TABLE bank_transactions (
    txn_id NUMBER PRIMARY KEY,
    account_no NUMBER,
    txn_amount NUMBER,
    txn_date DATE
) NO DROP NO DELETE;

-- Inserting a transaction
INSERT INTO bank_transactions VALUES (101, 1234567890, 5000, SYSDATE);

-- Trying to update or delete (This will fail!)
UPDATE bank_transactions SET txn_amount = 10000 WHERE txn_id = 101;
DELETE FROM bank_transactions WHERE txn_id = 101;
7

3. Operational Property Graphs – Native Graph Queries Inside Oracle

Graph databases have been gaining popularity in recent years. Facebook, LinkedIn, and Netflix all use graph databases to model social connections, recommendations, and fraud detection . Oracle 23ai brings graph capabilities natively into the database! You can now: - Store and analyze relationships without needing Neo4j or other graph databases. - Use SQL-like syntax to perform graph queries. - Improve fraud detection, supply chain analysis, and recommendation engines.
8

Example Use Case – Fraud Detection in Banking

Banks like ICICI use graph models to detect fraud patterns . If a credit card is used in Delhi at 10 AM and then in Mumbai at 10:15 AM , something shady is going on!
9

Code Example: Finding Friends of Friends in a Social Network

Graph queries can detect fraud, analyze supply chains, and build recommendation engines!

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Creating a graph table
CREATE PROPERTY GRAPH social_network 
    VERTEX TABLE users
    EDGE TABLE friendships
    SOURCE KEY (user_id)
    DESTINATION KEY (friend_id);

-- Finding friends of friends (2nd-degree connections)
SELECT friend_of_friend 
FROM TABLE(SEARCH_GRAPH(
    social_network 
    START WITH (user_id = 101) 
    CONNECT BY NOCYCLE NODES(2)
));
10

4. AI-Driven Automatic Index Optimization

How many times have you seen a DBA spending hours optimizing indexes , only to see the query performance degrade? Well, Oracle 23ai now uses AI to optimize indexes automatically! - It learns query patterns and creates/drops indexes dynamically. - No more over-indexing (which slows down updates). - No more under-indexing (which slows down queries).
11

Example: Let Oracle Handle Indexing for You!

DBAs, you can finally take that vacation without worrying about indexes!

Code/Command (click line numbers to comment):

1
2
3
4
5
-- Enabling automatic indexing
ALTER SESSION SET AUTO_INDEX_MODE = ON;

-- Checking auto-created indexes
SELECT * FROM DBA_AUTO_INDEXES;
12

5. Passwordless Authentication – Say Goodbye to Password Hassles!

We’ve all been there, forgot your password? Oracle 23ai now allows passwordless authentication , so you can log in using: - Biometric authentication (Face ID, fingerprint) - Hardware security keys (YubiKey, smart cards) - OAuth-based logins This reduces the risk of password leaks and improves security compliance for enterprises.
13

Example: Setting Up Passwordless Authentication

No more forgotten passwords = fewer helpdesk tickets!

Code/Command (click line numbers to comment):

1
2
3
-- Creating a user with passwordless login
CREATE USER admin IDENTIFIED EXTERNALLY AS 'biometric_auth';
GRANT CONNECT, DBA TO admin;
14

Why Should You Upgrade to Oracle 23ai?

Still on Oracle 19c or 21c ? Let me make this easy for you: - Want faster query performance? → Upgrade! - Need better security & compliance? → Upgrade! - Want developer-friendly features? → Upgrade! - Like doing things the hard way? → Stay where you are… but why suffer?

Comments (0)

Please to add comments

No comments yet. Be the first to comment!