Oracle 23c SQL Syntax for Efficient Data Manipulation: Table Value Constructor

vijay balebail
3 min readAug 23, 2023

--

Multiple rows
Multiple rows

In the ever-evolving landscape of database management, Oracle has unveiled a developer-friendly feature in its latest release, Oracle Database 23c. This feature, aptly named the “Table Value Constructor,” introduces SQL syntax that empowers developers to select and manipulate multiple rows with ease, leveraging the structure of a table. With this innovative approach, developers can now perform tasks like INSERT, SELECT, MERGE, and CREATE TABLE AS SELECT (CTAS) operations more efficiently and intuitively.

Table Value Constructor: Revolutionizing Data Insertion

In prior versions of Oracle, inserting multiple rows into a table required distinct INSERT statements for each row. With Oracle Database 23c, however, this cumbersome process has been streamlined. Now, developers can use the Table Value Constructor to perform bulk data insertion in a single statement. For instance, instead of writing three separate INSERT statements like this:

INSERT INTO test_table VALUES (1, 'Vijay');
INSERT INTO test_table VALUES (2, 'Milton');
INSERT INTO test_table VALUES (3, 'Henry');

The new syntax empowers developers to achieve the same result with remarkable simplicity:

INSERT INTO test_table
VALUES
(1, 'Vijay'),
(2, 'Milton'),
(3, 'Henry');

Optimizing Application Performance

Beyond the immediate advantages of simplified data insertion, the Value Constructor offers an essential benefit for applications inserting data row by row. In such scenarios, the significance of reduced round trips cannot be overstated. As the Value Constructor enables the insertion of multiple rows in a single trip to the database, the potential performance gain is substantial. By consolidating multiple rows into one statement, the database executes a single ‘parse and execute’ operation further enhancing performance.

Extending Value Constructor to SELECT Statements

The Value Constructor doesn’t stop at data insertion; it extends its benefits to SELECT and WITH clauses as well. Before Oracle Database 23c, the primary method for achieving similar functionality was through the use of UNION ALL. For example, to query data with the same logic as the previous insertion example, developers used to craft a SELECT statement like this.

SELECT 1 AS id, 'Vijay' AS name FROM dual
UNION ALL
SELECT 2, 'Milton' FROM dual
UNION ALL
SELECT 3, 'Henry' FROM dual;

Now, the Value Constructor brings remarkable simplicity to SELECT operations:

SELECT *
FROM
(VALUES
(1, 'Vijay'),
(2, 'Milton'),
(3, 'Henry')
) t1 (id, name);

Note that the new syntax to define the column name ( id, name) in the above example.

Diverse Applications: CTAS, MERGE, and More

The Value Constructor’s transformative power isn’t confined to INSERT and SELECT operations alone. It opens the door to more efficient operations across various contexts:

  1. CREATE TABLE AS SELECT (CTAS): Creating tables based on selected data becomes a breeze. The Value Constructor even allows developers to define datatypes directly. For instance:
CREATE TABLE test_CTAS AS
SELECT *
FROM
(VALUES
(1, CAST('Vijay' AS VARCHAR2(32))),
(2, 'Milton'),
(3, 'Henry')
) t1 (id, name);

2. MERGE Statements: The Value Constructor serves as a dynamic source for MERGE statements, simplifying updates and insertions. Here’s a glimpse of its power:

MERGE INTO emp e
USING
(VALUES
(1, 'Vijay'),
(2, 'Milton'),
(3, 'Henry')
) t2 (id, name)
ON (e.empno = t2.id)
WHEN MATCHED THEN
UPDATE SET e.name = t2.name
WHEN NOT MATCHED THEN
INSERT (empno, name)
VALUES (t2.id, t2.name);

3. Update Joins with Value Constructor: Complex update operations become significantly more intuitive and streamlined with the Value Constructor, as demonstrated below:

UPDATE emp e
SET e.sal = e.sal + a.sal
FROM
(VALUES
(7839, 9.9),
(7698, 9.9),
(7782, 9.9)
) a (id, sal)
WHERE e.empno = a.id;

Incorporating this syntax simplifies the logic of queries, reducing platform-specific syntax concerns and enabling smoother migration between databases.

In conclusion, Oracle Database 23c’s Value Constructor emerges as a game-changer, ushering in a new era of streamlined data manipulation. With its versatile applications, including bulk data insertion, SELECT operations, CTAS, MERGE statements, and more, developers can focus on the essence of their queries rather than grappling with complex syntax. As this feature gains traction in the developer community, it promises to facilitate seamless transitions between databases and contribute to a more efficient and productive coding experience.

--

--