Oracle Database 23ai New Features and Enhancements
23ai New Features
SELECT without FROM DUAL
Oracle Database 23ai, you can use SELECT without specifying dual table.
SELECT sysdate, 'Hello World' ; output : 30-JAN-24 Hello World
Use column alias in GROUP BY and HAVING
You can use column aliases and position numbers in GROUP BY and HAVING clauses.
/* GROUP BY on expression alias */
SELECT department_id AS did, SUM(salary) AS xyz FROM employees GROUP BY did;
/* GROUP BY on expression position number */
SELECT department_id AS did, SUM(salary) AS xyz FROM employees GROUP BY 1;
/* ORA-00979: "DEPARTMENT_ID": must appear in the GROUP BY clause or be used in an aggregate function 00979. 00000 - "not a GROUP BY expression" Cause:
Action: Error at Line: 37 Column: 5 */
ALTER SESSION SET group_by_position_enabled = true;
SELECT department_id AS did, SUM(salary) AS xyz FROM employees GROUP BY 1;
IF EXISTS and IF NOT EXISTS in DDL
Introduces new clauses (IF EXISTS and IF NOT EXISTS) in DDL statements to handle scenarios where objects may or may not exist, providing more control.
drop table if exists boolean_table ; create table if not exists boolean_table (val varchar2(10), flag boolean) ;
Note IF NOT EXISTS and REPLACE are mutually exclusive for any DDL. You will encounter ORA-11541 if try to achieve the same.
PGQL (Property Graph Query Language)
PGQL is a graph query language built on top of SQL, bringing graph pattern matching capabilities to existing SQL users as well as to new users who are interested in graph technology but who do not have an SQL background.
PGQL | Property Graph Query Language (pgql-lang.org)
PGQL 2.0 Specification | Property Graph Query Language (pgql-lang.org)
Value Constructor
Allows grouping multiple rows of data in a single DML or SELECT statement.
/*Multi insert using VALUE constructor*/ insert into boolean_table values ('false' , false), ('False' , False), ('FALSE' , FALSE), ('f','f'),('0',0); select * from boolean_table; select * from (values ('Santosh' , 1), ('Panigrahi' , 1), ('Trainer' , 3)) t1 (dsc, id); WITH T (C1, C2, C3) AS ( values (0, 1, 2), (3, 4, 5 ), (6, 7, 8) ) select * from t; /* C1 C2 C3 0 1 2 3 4 5 6 7 8 */
RETURNING clause for MERGE and also returning OLD column value for UPDATE
- Enables the use of
RETURNINGclause with theMERGEstatement and introduces the ability to access the original column value (OLDcolumn_name) after anUPDATEstatement. If you've used PL/SQL, you might recognize this feature. Now, it's available in SQL too. It lets you retrieve affected rows after a DML operation, perform calculations on affected columns, and access both old and new values.
SQL> select * from employees where employee_id = 100;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
______________ _____________ ____________ ________ _______________ _______________________ __________ _________ _________________ _____________ ________________
100 Steven King SKING 515.123.4567 17-JUN-2003 00:00:00 AD_PRES 24000 90
SQL> -- We will check returning cluse functionaly to return old and new values.
SQL> -- before 23ai we can get new value using return clause and both old and new using pl/sql construct.
SQL> variable l_id number
SQL> variable l_new_sal number
SQL> variable l_old_sal number
SQL> EXECUTE :l_id := 100;
PL/SQL procedure successfully completed.
SQL> UPDATE employees set salary=salary*2 where employee_id = 100
2* RETURNING new salary INTO :l_new_sal;
1 row updated.
SQL> PRINT l_new_sal
L_NEW_SAL
----------
48000
SQL> select * from employees where employee_id = 100;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
______________ _____________ ____________ ________ _______________ _______________________ __________ _________ _________________ _____________ ________________
100 Steven King SKING 515.123.4567 17-JUN-2003 00:00:00 AD_PRES 48000 90
SQL> roll
Rollback complete.
SQL> select * from employees where employee_id = 100;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
______________ _____________ ____________ ________ _______________ _______________________ __________ _________ _________________ _____________ ________________
100 Steven King SKING 515.123.4567 17-JUN-2003 00:00:00 AD_PRES 24000 90
SQL> UPDATE employees set salary=salary*2 where employee_id = 100
2* RETURNING old salary INTO :l_old_sal;
1 row updated.
SQL> PRINT
L_ID
----------
100
L_NEW_SAL
----------
48000
L_OLD_SAL
----------
24000
SQL> select * from employees where employee_id = 100;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
______________ _____________ ____________ ________ _______________ _______________________ __________ _________ _________________ _____________ ________________
100 Steven King SKING 515.123.4567 17-JUN-2003 00:00:00 AD_PRES 48000 90
SQL> roll
Rollback complete.
SQL> select * from employees where employee_id = 100;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
______________ _____________ ____________ ________ _______________ _______________________ __________ _________ _________________ _____________ ________________
100 Steven King SKING 515.123.4567 17-JUN-2003 00:00:00 AD_PRES 24000 90
SQL>
Better error messages to explain why a statement failed to execute
Enhances error messages to provide more detailed and helpful explanations for failed SQL statements.
Developer Role (DB_DEVELOPER_ROLE)
Introduces a simplified way to grant necessary privileges for database development roles, reducing the need for manual role assignments.
-- Instead of grant connect, resource to <developer user>
exec DBMS_DEVELOPER_ADMIN.GRANT_PRIVS(developer user);
JavaScript Stored Procedures
Based on the Multi-Language Engine (MLE) powered by GraalVM, Oracle Database 23ai allows the definition of stored procedures written in JavaScript.
SQL Domains
In Oracle Database 23ai, domains can be defined as database objects– as shown below – and when a column is defined, it can be linked to a domain definition. This is a potentially useful piece of metadata. But it is more than that: the check constraint defined against the domain is inherited by the column. The display format and order by expression can be used in query statements through the new functions DOMAIN_DISPLAY and DOMAIN_ORDER.
Update and Delete records based on a join condition
Provides a more elegant syntax for updating records based on a join condition, simplifying complex updates. Does not support ANSI syntax you can not mention the keywords JOIN and ON clause with this feature.
Use Case: the ability to copy values from a temporary table or a staging table into a main table by using ETL. We might also want to aggregate child totals into a parent row. We might also want to remove or change
select * from employees where employee_id=200; UPDATE employees e SET e.department_id = 270 FROM departments d WHERE e.department_id = d.department_id AND e.employee_id = 200; -- This will update department_id from 10 to 270 select * from employees where employee_id=200;
Schema Level Privileges
Allows granting access on all objects in a schema with a single statement, simplifying privilege management. The ability to grant access on allobjects in an schema in a single statement For example GRANT SELECT ANY TABLE ON SCHEMA TO <user name>
Ability to define 4096 columns in a table
In Oracle Database 23ai, it is possible to define up to 4096 columns in a table, catering to scenarios where a large number of columns are needed, such as in machine learning models.
BOOLEAN Data Type
Introduces the BOOLEAN data type for column definitions, allowing testing on boolean values directly in the WHERE clause without explicitly comparing to TRUE.
/*BOOLEAN*/
create table boolean_table (val varchar2(10), flag boolean) ;
-- True
insert into boolean_table values ('true' , true );
insert into boolean_table values ('True' , True );
insert into boolean_table values ('TRUE' , TRUE );
insert into boolean_table values ('yes','yes' );
insert into boolean_table values ('l', 1);
insert into boolean_table values ('t','t' );
insert into boolean_table values ('ON','ON' );
-- False
insert into boolean_table values ('false' , false);
insert into boolean_table values ('False' , False);
insert into boolean_table values ('FALSE' , FALSE);
insert into boolean_table values ('f','f');
insert into boolean_table values ('0',0);
insert into boolean_table values ('OFF','OFF' );
-- NULL test
insert into boolean_table values ('null', null );
-- Test
select * from boolean_table;
select * from boolean_table where flag;
select * from boolean_table where not flag;
select * from boolean_table where flag is null;
Asynchronous Transaction
Oracle Database 23ai introduces a new feature called Asynchronous Transactions, which allows multiple transactions to concurrently update a numeric column marked as reservable[4]. This feature increases developer productivity by enabling REST API applications to take advantage of Transactional Event Queues (TxEventQ) to handle application and data events[3].
Key features of Asynchronous Transactions in Oracle Database 23ai include:
Lock-free reservations: A numeric column marked as reservable allows multiple transactions to concurrently update the value[4].
Transactional Event Queues (TxEventQ): This feature enables Oracle Database to handle application and data events, making it suitable for use in Machine Learning applications and other data-intensive applications[3].
Asynchronous programming and Oracle Database pipelining: These features remove performance bottlenecks and enhance overall responsiveness, making it easier for developers to incorporate them into their existing applications[5].
Asynchronous Transactions in Oracle Database 23ai can be used in various scenarios, such as repetitive batch operations, looping through sets of records for updates, and inserts with a commit for each set of records[3]. This feature can help improve application performance and developer productivity by allowing multiple transactions to concurrently update a numeric column marked as reservable[4].
create table if not exists tbl_async
(
id_number integer primary key,
val NUMBER(5, 2) RESERVABLE
);
INSERT into tbl_async VALUES
(1, 18.20),
(2, 20.21),
(3, 24.22) ,
(4, 25.24);
SELECT * FROM tbl_async WHERE id_number = 1;
UPDATE tbl_async SET val = val + 10 WHERE id_number = 1;
SELECT * FROM tbl_async WHERE id_number = 1; -- Here val is still 18.20 beacuse transaction is not yet COMMIT and table column define as RESERVABLE
commit;
drop table tbl_async; -- was giving error initially don't know why it is now not throwing any
NOTE : You can not DROP or MOVE tables with RESERVABLE columns. to drop the table you have to modify column to NOT RESERVABLE
ALTER TABLE tbl_async MODIFY val not RESERVABLE;
Blockchain Tables
Oracle Database 23ai provides support for blockchain tables or insert only table, allowing the use of crypto-secure methods to protect data from tampering or deletion by external hackers
SQL transpiler
These enhancements contribute to improving the functionality, usability, and expressiveness of Oracle Database 23ai. till now what I understand is any plsql construct if possible can be converted to SQL format. wip..
FOOR and CEIL on dates
-- find the upper/lower bound values for datetime
-- and interval values in given time
SELECT SYSDATE, FLOOR (SYSDATE, 'IYYY'), CEIL (SYSDATE, 'IYYY') FROM DUAL;
/*
SYSDATE FLOOR(SYSDATE,'IYYY') CEIL(SYSDATE,'IYYY')
1/30/2024 12:36:27 PM 1/1/2024 12/30/2024
*/
DEFAULT ON NULL FOR UPDATE Statement
In Oracle 23ai, a powerful new feature has been introduced - the DEFAULT ON NULL option for the UPDATE statement. This eliminates the need for triggers that were previously used to achieve similar functionality. While the INSERT statement already had this feature, it's now extended to cover updates as well. Let's dive into a practical example:
-- Creating a table with the DEFAULT ON NULL feature
SQL> create table demo_t (
2 id number,
3 ename varchar2(100) DEFAULT ON NULL for insert and UPDATE 'missing name'
4* );
-- Inserting values into the table
SQL> insert into demo_t values (1, default);
SQL> insert into demo_t values (2, default);
-- Checking the contents of the table
SQL> select * from demo_t;
ID ENAME
_____ _______________
1 missing name
2 missing name
-- Updating the value and observing changes
SQL> update demo_t set ename = 'Check for null' where id=1;
SQL> update demo_t set ename = null where id=1;
-- Verifying the final results
SQL> select * from demo_t;
ID ENAME
_____ _______________
1 missing name
2 missing name
-- Creating a table with GENERATED BY DEFAULT ON NULL
SQL> create table t1 (c1 number, c2 number generated by default on null for insert and update as identity);
-- Inserting values into the table
SQL> insert into t1(c1) values(10);
SQL> insert into t1(c1) values(11);
-- Updating and observing changes
SQL> update t1 set c1=3 where c1=10;
SQL> update t1 set c1=4, c2 = NULL where c1=11;
-- Verifying the final results
SQL> select * from t1;
C1 C2
_____ _____
3 1
4 3
These enhancements streamline your SQL operations, providing more concise and efficient ways to handle default values on null conditions during both insertions and updates. Oracle 23ai continues to pave the way for improved database management.
Annotations – define metadata for database objects
Introduces annotations for defining metadata on various database objects like tables, views, columns, indexes, PL/SQL program units, and materialized views.
Annotations simplify sharing application metadata with external applications by allowing the addition of metadata on database objects. This metadata can include instructions on how to display, format, mask, and hide/unhide column values.
The functionality of Annotations is comparable to Java Annotations, providing benefits such as sharing application metadata across different modules and microservices, improving SQL code maintenance, and enhancing the developer experience.
An example demonstrates the use of annotations in creating a table named "departments_demo," where annotations are applied to different columns. For instance, the department_id column is annotated to be displayed as "Dept ID" and marked as an identity column, while the manager_id column is annotated to be displayed as "Manager ID" with a UI_Hidden annotation.
create table departments_demo
( department_id NUMBER ANNOTATIONS (Identity, Display 'Dept ID', Group 'Dept Info'),
department_name varchar2(25) ANNOTATIONS(Display 'Dept Name', Group 'Dept Info'),
manager_id NUMBER ANNOTATIONS (Display 'Manager ID', Group 'Dept Info', UI_HIDDEN) )
ANNOTATIONS (Display 'Department Table', SINCE_APPLICATION_VERSION '1.0.1');
Aggregation over INTERVAL types
JSON Schema Support
what is JSON schema support? Well, it lets us define the rules, also known as the allowed properties for JSON documents. And we can do this either using the IS JSON constraint. Or we can do validations as well with a PL/SQL utility function. And of course, the benefits are that it helps us avoid errors in production that we might have missed in development.
We are doing a validation-- you'll notice the IS JSON VALIDATE keywords. And what we're doing here is we are defining the rules of the data coming into that column. You'll notice that a type is an object, and the minimum properties have to be 2.
Now let's take a look at a simple validation using what we call shorthand syntax where we don't have to use the constraint keyword like we did before. You'll notice that we have the same CREATE TABLE statement with json_tab being the name of the table. But this time, you'll notice that we don't have a constraint keyword. We have a jval column of the JSON type. And you'll notice that we've included the VALIDATE USING clause.
SODA Enhancements
Enhancement to JSON_TRANSFORM
Version 23ai introduces significant enhancements to JSON_TRANSFORM, including support for right-hand-side path expressions, nested paths, and arithmetic operations. JSON_TRANSFORM now serves as the primary operator for modifying JSON data and supports sorting arrays. These enhancements increase update capabilities, establish parity with other systems like MongoDB, and enable declarative transformations without procedural language changes, reducing I/O.
Comparing and Sorting JSON Datatypes
In 23ai, comparing and sorting JSON data types is now possible, building on the introduction of the JSON data type in 21C. This functionality extends to various SQL clauses including WHERE, ORDER BY, and GROUP BY. The benefits include enhanced SQL and JSON programming capabilities, avoidance of unexpected data type conversion issues, and alignment with features offered by non-Oracle systems, thereby providing a similar capability in 23ai.
Another 23ai examples -
Predicates for JSON_VALUE and JSON_QUERY
In 23ai, predicates for JSON value and JSON query have been enhanced, allowing developers to utilize conditional logic within path expressions. Multiple predicates are now supported for all path expressions, expanding the capabilities beyond the previous limitation to only one predicate.
Furthermore, a passing clause has been introduced, enabling the use of bind variables to hold path expressions and literals. This feature is supported in JSON_QUERY, JSON_VALUE, JSON_TRANSFORM, and JSON_EXISTS.
The benefits of these enhancements include improved power and flexibility for developers working with SQL and JSON, better compatibility with operations from other SQL and JSON databases, and the ability to execute more complex queries compared to previous versions.
In the above example, we created jtab table with a JSON column ('J'). Three phone numbers (work, home, mobile) are inserted as an array. Using the passing clause, we then searched for the work number using JSON query predicates.
JSON-Relational Duality View
In 23ai, JSON-Relational Duality View feature addresses challenges in storing data by bridging the gap between different data representation methods. Traditionally, data is stored in various formats such as object databases, object-relational databases, or document databases, which can pose challenges for complex applications and evolving data structures.
The object-relational impedance mismatch arises when applications manipulate data differently from relational databases. However, JSON-Relational Duality View tackles this mismatch by integrating relational database structures with JSON data. Relational databases store data in normalized tables, avoiding redundant copies and enabling flexible querying, joining, and updating through normalization principles.
Introduction of data quality operators
fuzzy_match and phonic_encode using different algorithms.
JSON Materialized Views Enhancements
Vectorized Query Processing
In 23ai, vectorized query processing is a feature that enhances in-memory performance by leveraging the in-memory deep vectorization framework introduced in previous versions of the database. It automatically improves performance without requiring any special changes to SQL queries, as the usage is determined by the database.
To check if vectorized query processing was used, one can utilize SQL Monitor. However, there are some restrictions associated with this feature, such as no support for bushy joins, parallel query hash-hash plan support, or left semi/outer support for non-root right-deep joins. Additionally, there are aggregation restrictions, including limitations on using the same column as grouping columns and aggregation columns, and limited expression support for aggregation, which only allows arithmetic expressions on numeric data type columns.
Furthermore, aggregation is supported by single table PCODE but not HPK for SQL, and functions like CASE, COALESCE, DECODE, NVL, and DISTINCT have restrictions. Deduplication per IMCU is currently supported, and ordering is possible on the column from the last probe table with the rank with the ORDER BY clause.
Staging Tables
In Oracle 23ai, staging tables are introduced for optimized data loading into data warehouses. They can be created as new tables or by altering existing ones. Staging tables bypass the recycle bin and have restrictions such as no data compression. After data loading, they are truncated or deleted.
If you want to revert the stagging table to the original (not stagging) then you can ALTER TABLE in the same way this example
ALTER TABLE stagging_table NOT FOR STAGGING.;
LPCT for Materialized View Refresh and Staleness Tracking
This operates at the level of logical granularity and gives you the flexibility to align the boundaries of logical partitions with the business rules and with the changes applied to tables. This optimizes MV for partial query rewrite. It also optimizes incremental refresh, without the overhead of MV log.
SYSDATE and SYSTIMESTAMP
Some of the ppt slides and snippets are from oracle-database-23ai-new-features-for-developers course**.**
