At Monin, we’re always on the lookout for the latest advancements to give our clients a competitive edge. Recently, Robert Pastijn, an expert from Oracle, visited Monin to share insights about Oracle’s newest release, Oracle 23ai. This release promises to shake up the world of database management with cutting-edge AI features and enhancements designed to make database operations smoother and more efficient. In this overview, we’ll highlight some of the key points discussed during Robert’s visit and explore what makes Oracle 23ai so exciting for both developers and DBAs. 

Why Oracle 23ai?

Oracle 23ai was designed to meet the evolving needs of modern businesses, especially in leveraging AI and optimizing database functionalities. Originally anticipated as Oracle 23C, the focus shifted significantly, leading to the enhanced AI-centric release we have now. Here are some of the key highlights:

Key SQL Features

Select Without ‘From Dual‘: Before, retrieving system dates or running certain functions needed the dummy table ‘dual’. With Oracle 23ai, you can perform these operations more cleanly:

                  — Old way
                  SELECT sysdate FROM dual;

                  — New way
                  SELECT sysdate;

Group By with Alias: Grouping by complex expressions can now be simplified using aliases, improving code readability and reducing errors. Additionally, you can also group by column number. For example, in the query below, you could use GROUP BY 1 to group by the first column in the SELECT statement:

                  — Old way
                  SELECT SUBSTR(column, 1, INSTR(column, ‘ ‘) – 1) AS first_name 
                  FROM employees
                  GROUP BY SUBSTR(column, 1, INSTR(column, ‘ ‘) – 1);

                  — New way
                  SELECT SUBSTR(column, 1, INSTR(column, ‘ ‘) – 1) AS first_name 
                  FROM employees
                  GROUP BY first_name;

Annotations: Create detailed annotations at the column level to provide additional context and metadata. These annotations can now be easily queried, allowing you to search for specific data categories, such as all GDPR-related data.

                    create table monin (
                                ID number,
                                emp_name varchar2(50) ANNOTATIONS ( description ’employee name’, classification ‘CONFIDENTIAL’, label ‘Full Name’),
                                emp_sal number ANNOTATIONS ( description ’employee gross salary’, classification ‘HIGHLY CONFIDENTIAL’, label ‘Gross Salary’)
                                 );
                    alter table monin ANNOTATIONS ( description ‘monin employee info’)

Domains: Define reusable domains to enforce consistent constraints across multiple columns and tables. For example, you can create a domain for email formats or enforce GDPR compliance rules.

Multi Inserts: This feature allows multiple insertions with a single statement, enhancing performance and reducing the complexity of bulk insert operations:
                      INSERT INTO table_name (column1, column2) VALUES 
                      (value1, value2), 
                      (value3, value4); 

AI-Driven Features 

Oracle 23ai introduces several AI-centric capabilities that streamline data management and analytics: 

Select AI: This innovative feature lets you query the database using natural language. Simply ask a question, and Oracle translates it into a SQL query to fetch the results. Though it currently requires descriptive table and column names and is limited to English, it’s a promising step toward more intuitive data interactions. Check out the following demo if you want to see it in action. 

Vector Datatypes: Embedding vector search capabilities directly into the Oracle database eliminates the need for separate vector databases. This is particularly beneficial for applications requiring similarity search capabilities, like recommendation systems or Retrieval Augmented Generation (RAG) applications. This video has more information on Oracle’s vector search capabilities. 

JSON and Duality Views 

Oracle 23ai enhances JSON support by introducing binary JSON storage and duality views, which allow JSON data to be handled as both document and relational data. This hybrid approach optimizes storage and query performance while maintaining the flexibility of JSON documents. It also eliminates the need for a separate NoSQL database, reducing both administrative complexity and the associated application overhead. 

Better Error Handling 

Oracle 23ai significantly improves error handling, making debugging much easier. Instead of generic error messages, you now get detailed information about the specific table, column, or expression causing the issue. This saves time and reduces frustration, particularly when dealing with complex queries and large databases. 

Advanced Resource Management 

Data Guard at PDB Level: Selective replication of pluggable databases (PDBs) within a container database (CDB) optimizes resource use and storage costs.

True Cache: Enhances data availability and performance by replicating active memory blocks across sites without needing additional storage. Ideal for environments with remote processing needs.

Internal Instance Resource Management: Manage resource allocation at both the CDB and PDB levels. Set CPU and memory limits to ensure no single database hogs resources, thus maintaining overall system performance and stability. 

Enhanced Security 

SQL Firewall: Previously a separate product, the SQL firewall is now integrated into Oracle 23ai. It helps prevent SQL injection attacks by learning typical query patterns and blocking anomalies.

Schema-Level Grants: Grant permissions at the schema level rather than individual tables, simplifying security management.

Developer Role: A new role tailored for developers that provides the necessary permissions to create and query tables without the ability to alter data, ensuring a secure development environment. 

Conclusion 

Oracle 23ai isn’t just an upgrade; it’s a transformation in how databases can be managed and used for modern applications. As part of Oracle’s vision for converged databases, 23ai brings together relational, vector, document (with JSON), and graph data models into a single platform. This approach reflects Oracle’s commitment to simplifying data management while enabling businesses to meet the diverse demands of modern development with a unified, efficient solution.  

At Monin, we’re excited to help our clients harness the power of Oracle 23ai. Whether you’re a developer looking to streamline your workflows or a DBA aiming to optimize your database environment, Oracle 23ai has tools and features that cater to all your needs. Contact us today to learn how we can assist you in implementing Oracle 23ai in your organization.