The latest Innovation release of the world's most popular database, Oracle Database 21c, is now generally available "cloud first" in the Oracle Cloud Database Service Virtual Machine (for RAC and single instance) and Bare Metal Service (single instance). It's also available in the Autonomous Database Free Tier Service in Ashburn (IAD), Phoenix (PHX), Frankfurt (FRA) and London (LHR) regions. General availability of Oracle Database 21c on other platforms including Exadata, Linux, Windows and the free Express Edition (XE) has since followed therafter.
Oracle has consistently taken the approach that storing and managing data in a converged database is more efficient and productive than breaking up into multiple single-use engines - which inevitably results in data integrity, consistency and security issues. Simply put, a converged database is a multi-model, multi-tenant, multi-workload database. Oracle Database fully supports multiple data models and access methods, simplifies consolidation while ensuring isolation, and excels in typical database workload use cases - both operational and analytical. Click the image below for a video introduction to Oracle's converged database.
The 21c generation of Oracle's converged database therefore offers customers: best of breed support for all data types (e.g. relational, JSON, XML, spatial, graph, OLAP, etc.), and industry-leading performance, scalability, availability and security for all their operational, analytical and other mixed workloads. Oracle's converged strategy also ensures that developers benefit from all Oracle Database 21c key capabilities (e.g. ACID transactions, read consistency, parallel scans and DML, online backups, etc.) - freeing them to focus on developing applications without having to worry about data persistence.
This latest Innovation release introduces a number of new features and enhancements that further extend database use cases, improves developer, analyst and data scientist productivity, and increases query performance. Listed below is a subset of what's new in Oracle Database 21c. For a more comprehensive review please refer to the New Features Guide or the Database Features & Licensing App. Or, try for yourself with the Oracle Database 21c new features LiveLab workshop!
Blockchain as a technology has promised much in terms of solving many of the problems associated with the verification of transactions. While considerable progress has been made in bringing this technology to the enterprise, a number of problems exist. Arguably, the largest being the complex nature of building applications that can support a distributed ledger. Oracle Database 21c addresses this problem with the introduction of Blockchain Tables. These tables operate like any normal heap table, but with a number of important differences. The most notable of these being that rows are cryptographically hashed as they are inserted into the table, ensuring that the row can no longer be changed at a later date.
This essentially creates an insert only table, and users are unable to update or delete Blockchain Table rows. In addition, users are also prevented from truncating data, dropping partitions or dropping Blockchain Tables within certain time limits. These important capabilities mean that other users can trust that the data held in a Blockchain Table is an accurate record of events.
Oracle introduced support for JSON in Oracle Database 12c, storing JSON data as a VARCHAR2 or a LOB (CLOB or BLOB). This enabled developers to build applications with the flexibility of a schemaless design model, with all the power of Oracle Database. For example, users could query JSON documents using standard SQL, take advantage of advanced analytics, index individual attributes or whole documents, and process billions of JSON documents in parallel. Oracle also provided tools to discover what attributes make up the JSON documents, and thereby trivially create relational views on top of the collections. It was also possible for developers to treat their Oracle Database as if it were a NoSQL database by accessing it with the SODA (Simple Object Data API) APIs available for Java, Node.js, Python, C and REST.
In Oracle Database 21c, JSON support is further enhanced by offering a native data type, "JSON". This means that instead of having to parse JSON on read or update operations, the parse only happens on an insert and the JSON is then held in an internal binary format which makes access much faster. This can result in read and update operations being 4 or 5 times faster and updates to very large JSON documents being 20 to 30 times faster.
CREATE TABLE j_order ( id INTEGER PRIMARY KEY, po_doc JSON );
The new data type wasn't the only change that got introduced for JSON in Oracle Database 21c, Oracle also added a new JSON function JSON_TRANSFORM which makes it much simpler to update and remove multiple attributes in a document in a single operation.
UPDATE j_order SET po_doc = JSON_TRANSFORM( po_doc, SET '$.address.city' = 'Santa Cruz', REMOVE'$.phones[*]?(@.type == "office")' ) WHERE id = 555;
Oracle has also added compatibility for the new JSON datatype to integration drivers and utilities like Datapump and GoldenGate.
It is not unusual for a SQL statement to grow in complexity as the number of joins increase, or the operations performed on the retrieved data becomes more involved. It is also not uncommon for developers to try and solve this problem by using stored procedures and table functions to simplify these commonly used operations. This works extremely well to simplify code, but can potentially sacrifice performance as the SQL engine switches context with the PL/SQL Engine. In Oracle Database 21c, SQL Macros solve this problem by allowing SQL expressions and table functions to be replaced by calls to stored procedures which return a string literal to be inserted in the SQL we want to execute. It's an incredibly simple concept and one that C and Rust programmers will be familiar with. The following trivial example shows it in action.
First, let's create a table and insert a few rows.
CREATE TABLE line_items ( id NUMBER, name VARCHAR2(30), item_type VARCHAR2(30), price FLOAT ); INSERT INTO line_items VALUES (1, 'Red Red Wine', 'ALCOHOL', 15.6); INSERT INTO line_items VALUES (2, 'Its Cold Out There Heater', 'RADIATOR', 200.49); INSERT INTO line_items VALUES (3, 'How Sweet It Is Cake', 'FOOD', 4.56); COMMIT;
The SQL below calculates the value added tax on rows in our LINE_ITEMS table
SELECT id, CASE WHEN item_type = 'ALCOHOL' THEN ROUND(1.2 * price, 2) WHEN item_type = 'SOLAR PANEL' THEN ROUND(1.05 * price, 2) WHEN item_type = 'RADIATOR' THEN ROUND(1.05 * price, 2) ELSE price END AS total_price_with_tax FROM line_items;
However, Oracle Database 21c can simplify by creating a function with the new SQL_MACRO keyword and returning a string.
CREATE OR REPLACE FUNCTION total_price_with_tax(the_price FLOAT, the_item_type VARCHAR2) RETURN VARCHAR2 SQL_MACRO(SCALAR) IS BEGIN RETURN q'[CASE WHEN the_item_type = 'ALCOHOL' THEN ROUND(1.2 * the_price, 2) WHEN the_item_type = 'SOLAR PANEL' THEN ROUND(1.05 * the_price, 2) WHEN the_item_type = 'RADIATOR' THEN ROUND(1.05 * the_price, 2) ELSE the_price END]'; END; /
Developers then simply reference the SQL Macro inside a select statement. The SQL that's executed is exactly the same as the original SQL Statement without the overhead of a context switch each time the row is fetched to execute this function.
SQL> SELECT id, total_price_with_tax(price, item_type) AS total_price_with_tax FROM line_items; ID TOTAL_PRICE_WITH_TAX ---------- -------------------- 1 18.72 2 210.51 3 4.56
It's also worth noting that developers can use the same approach when creating Parameterized Views and Polymorphic Tables.
Analyzing data using a columnar model can result in massive performance improvements as compared to using a row-based model. However, updating data is significantly faster when using data held in rows. Oracle Database In-Memory is unique in that it allows users to benefit from both approaches. With this capability users can run their applications unchanged and Oracle Database In-Memory will maintain a columnar store supporting blazingly fast real-time analytical queries. The Oracle Database In-Memory Blog is a fantastic resource for finding out more about this powerful technology.
Oracle Database 21c introduces three major improvements to enhance performance and ease of use when using Oracle Database In-Memory:
Hybrid Columnar Scan
Please refer to the Oracle Database 21c New Features Guide for complete (long) list, but here are a few more notable enhancements that will be of interest to DBAs, developers, analysts and data scientists:
Oracle Database 21c is an Innovation release for customers to innovate faster with new and enhanced functionality on workload use cases and applications that could benefit accordingly. Customers should be aware that unlike Long Term releases, Innovation releases have a limited support window (typically ~2-years). Therefore, in the interests of maintaining business continuity, customers still running on prior releases (e.g. 11gR2, 12cR1, 12cR2, 18c) are encouraged to upgrade to Oracle Database 19c - the current Long Term release with support through to April 2027.
For the latest Oracle Database 21c availability and support windows on all on-premise platforms (including Exadata) and in Oracle Cloud (including Autonomous Database Services) please refer to MyOracle Support (MOS) note 742060.1.
William Hardie is Vice President of Oracle Database Product Management. He has been in IT for more than 30 years, and has specialized in relational database technologies for more than 25 years. William has worked with Oracle Database since version 5 and is a regular speaker at Oracle events and User Group Conferences. He is currently a member of the Quest Oracle User Group database technology advisory board.