In this post we'll take a look at some of the new features in Oracle Database 20c which has been refreshed to version 20.3 on the Oracle Cloud's DBCS platform.
Oracle Database 20c is the latest release of Oracle's converged (or multi-model) database. Like the previous releases, Oracle Database 20c provides industry leading performance, scalability, availability and security for OLTP, analytical and mixed workloads. It supports relational, JSON, XML, spatial, graph, columnar, OLAP and unstructured data, enabling you to focus on building applications without having to worry about how to persist such data.
You can find details on the significant features of previous release in the following posts:
Oracle Database 20c is an innovation release allowing users to try out new functionality, or take advantage of cutting-edge features for applications that could benefit accordingly. While we are very excited about this latest release, Oracle recommends that most users should consider upgrading to Oracle Database 19c, since it provides long term support all the way through to April 2027. You can check on the current support windows for all releases on the My Oracle Support web site article (Document ID 742060.1).
Since the initial releases of Oracle Database, Oracle has taken the approach that storing and managing data in a single database platform makes more sense than breaking it up and storing it in single use engines. Using multiple independent engines inevitably results in issues with data integrity, consistency and security. By using a single engine that provides the best of breed support for all of the major data types and development paradigms, users can benefit from all of Oracle Database's key capabilities such as, ACID transactions, read consistency, centralised security model, parallel scans and DML, online backups, point in time recovery etc. - regardless of the approach you take to storing the data.
The decision to centralize your data inside Oracle Database doesn't mean sacrificing the ability to build applications using whatever design approach you think is appropriate. Oracle supports the creation of single database application as well as those adopting event-driven or microservice paradigms. Key to this approach is the use of Oracle's Multitenant Architecture to provide each service with its own virtual database (PDB). This still allows you to manage many PDBs as one, and it simplifies the federation of the database via inter PDB SQL operations.
And, if you need to support millions of concurrent users or geographically distribute your database because of regulatory requirements, Oracle Database Sharding makes it simple to do this while still providing a converged data model.
At the start of this year, Oracle announced that Oracle Spatial and Graph, and Oracle Machine Learning are now part of the core database offering, making it even simpler and more cost-effective to analyse your data in a single location.
Oracle Database 20c introduces several features, far more than is covered in this short blog posting. Therefore, for a more comprehensive review, please take a look at the New Features Guide and also at the new Database Features and Licensing App.
Let's go through some of the significant enhancements in Oracle Database 20c Preview release.
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, with arguably the largest being the complex nature of building applications that can support a distributed ledger.
To simplify the introduction of this exciting technology in Oracle Database 20c we're introducing 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. Blockchain Tables don't allow users to update or delete rows. Users are also prevented from truncating the data, dropping partitions or dropping the table within certain time limits.
This important capability means that other users can trust that the data held in the blockchain table is an accurate record of events. Oracle Database 20c enables you to run a process that will verify all of the records are consistent with their hash signature.
For a more in depth break down of this critical capability, take a look at Mark Rakhmilevich's in depth blog on Blockchain tables.
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 some performance as the SQL engine switches context with the PL/SQL Engine. In Oracle Database 20c, 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 tables and insert a few rows.
CREATE TABLE(id integer, 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);
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 in Oracle Database 20c we can simplify it 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 varchar) return varchar2 SQL_MACRO(SCALAR) is begin return q'[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]'; end;
We can then simply reference the SQL Macro inside of 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 our function.
SQL > select id, total_price_with_tax(price, item_type) from line_items; ID TOTAL_PRICE_WITH_TAX(PRICE,ITEM_TYPE) ------------------------------------------ 1 18.72 2 210.5145 3 4.56
The same approach can be used in parametrised views and Polymorphic tables.
Keith Laker has a great presentation providing more info on this really useful piece of functionality.
Analysing data using a columnar model can result in massive performance improvements when compared to doing the same operations 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 you to benefit from both approaches. With this capability you can run your relational or JSON application unchanged and Oracle Database 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 20c introduces three major improvements to enhance performance and ease of use when using Oracle Database In-Memory functionality.
Hybrid Columnar Scan
We introduced support for JSON in Oracle Database 12c (18.104.22.168). It allowed JSON to be stored in the database inside of a varchar2 or a LOB (CLOB or BLOB). This meant it was possible to build applications with the flexibility offered by a schemaless design model but benefiting from the power of the Oracle Database. You could query the JSON documents using standard SQL, take advantage of advanced analytics, index individual attributes or whole documents and process billions of JSON documents in parallel. We also provided tools to discover what attributes made up the JSON documents and trivially create relational views on top of the collections. It was also possible for developers to treat the 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 20c we are improving our JSON support 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 20c 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;
And of course, we also added compatibility for the new JSON datatype to our drivers and utilities like Datapump and GoldenGate.
Machine Learning has been built into Oracle Database since the release of 8i. It doesn't require analysts or data scientists to extract the data onto a file system or specialist database, but rather allows you to leverage the power of Oracle Database and build models with over 30 Machine Learning algorithms running directly on data held in your tables. This approach of moving the algorithms to the data minimizes or eliminates data movement, achieves scalability, preserves data security, and accelerates time-to-model deployment.
In Oracle Database 20c we are introducing functionality to make it even simpler for users to take advantage of this functionality by providing Python Machine Learning interfaces to Oracle Database. This new client compliments the R and SQL interfaces already available. Data Scientists can now work in an environment they feel comfortable with, and simply treat the Oracle Database as a high performance compute engine. You can use programmatic structures similar to those in the Scikit-learn, and Panda frameworks which become simple proxies for tables in the database and then call the Oracle Database ML algorithms using Python function calls rather than SQL.
Oracle Machine Learning for Python also looks to simplify the process of selecting and tuning the right model which can be a complicated and time-consuming exercise. New AutoML functionality can select the model, the relevant attributes and then tune the hyper parameters. This dramatically reduces the time required to create a model that accurately creates predictions or classifies your data.
In Oracle Database 20c Machine Learning we are also adding support for the MSET-SPRT and XGBoost algorithms, and the Adam Optimization solver for the Neural Network Algorithm.
There were over 100+ enhancements to Oracle Database 20c. And whilst I'd love to cover them all in detail I'd simply be repeating a lot of content from Oracle Database New Features Guide. However there are a few more features that I think are of particular note.
Oracle Database 20c Preview is available on Oracle OCI's Database Cloud Service (DBCS) Virtual Machine now. I've just touched on some of the exciting new features available. For more information please take a look at Oracle Database New Features Guide.
For a trial of the Oracle Cloud or access to our free tier please check follow this link