Performance Features

• Automatic Zone Maps

  Automatic zone maps are created and maintained for any user table without any customer intervention. Zone maps allow the pruning of block ranges and partitions based on the predicates in the queries. Automatic zone maps are maintained for direct loads, and are maintained and refreshed for any other DML operation incrementally and periodically in the background.

  The feature is enabled as follows:

  exec dbms_auto_zonemap.configure('AUTO_ZONEMAP_MODE','ON');

  The feature is disabled as follows:

  exec dbms_auto_zonemap.configure('AUTO_ZONEMAP_MODE','OFF');

  See Summary of DBMS_AUTO_ZONEMAP Subprograms for more information.

• Object Activity Tracking System

  Object Activity Tracking System (OATS) tracks the usage of various types of database objects. Usage includes operations such as access, data manipulation, or refresh.

  No manual intervention is required to enable OATS, and zero or minimal configuration is required. See PL/SQL procedure DBMS_ACTIVITY.CONFIGURE and database dictionary views DBA_ACTIVITY_CONFIG for details.

Application Development: Advanced Analytical SQL Capabilities

• SQL Macros

  SQL Macros, the capability to factor out common SQL constructs supports scalar expressions, increasing developer productivity, simplify collaborative code development, and improve code quality. See SQL Macros for more information.

• Enhanced Analytic Functions

  Window functions support the full ANSI Standard, including the support of EXCLUDE options and the WINDOW clause. Supporting the full ANSI standard enables easier migration of applications that were developed with other standard-compliant database systems. See Windowing Functions for more information.

• New Analytical and Statistical Aggregate Functions

  Several new analytical and statistical aggregate functions are available in SQL in Oracle Database 21c. With these additional SQL aggregation functions, you can write more efficient code and benefit from faster in-database processing.

  • CHECKSUM computes the checksum of the input values or expression.

    Supports the keywords ALL and DISTINCT.

  • KURTOSIS functions KURTOSIS_POP and KURTOSIS_SAMP measure the tailedness of a data set where a higher value means more of the variance within the data set is the result of infrequent extreme deviations as opposed to frequent modestly sized deviations. Note that a normal distribution has a kurtosis of zero.

    Supports the keywords ALL, DISTINCT, and UNIQUE.

  • SKEWNESS functions SKEWNESS_POP and SKEWNESS_SAMP are measures of asymmetry in data. A positive skewness is means the data skews to the right of the center point. A negative skewness means the data skews to the left.

    Supports the keywords ALL, DISTINCT, and UNIQUE.

  • ANY_VALUE, a function to simplify and optimize the performance of GROUP BY statements, returns a random value in a group and is optimized to return the first value in the group. It ensures that there are no comparisons for any incoming row and eliminates the necessity to specify every column as part of the GROUP BY clause.

  See Oracle Database 21c SQL Language Reference Guide for more information.

• Bitwise Aggregate Functions

  With the new bitwise type processing functions BIT_AND_AGG, BIT_OR_AGG, andBIT_XOR_AGG, native bitwise type processing is provided by Oracle Database 21c. These functions enable a type of processing inside the database for new types of application processing, improving the overall performance, avoiding unnecessary data movement, and natively taking advantage of core database functionality such as parallel processing. See Oracle Database 21c SQL Language Reference Guide. for more information.

JavaScript Execution using DBMS_MLE

The DBMS_MLE package allows users to execute JavaScript code inside the Oracle Database and exchange data seamlessly between PL/SQL and JavaScript. The JavaScript code itself can execute PL/SQL and SQL through built-in JavaScript modules. JavaScript data types are automatically mapped to Oracle Database data types and vice versa.

With the DBMS_MLE package, developers can write their data processing logic in JavaScript. JavaScript is a widely-used and popular programming language that can now also be used for writing programs that need to execute close to the data.

See DBMS_MLE for more information.

Blockchain Table

Blockchain tables are append-only tables in which only insert operations are allowed. Deleting rows is either prohibited or restricted based on time. Rows in a blockchain table are made tamper-resistant by special sequencing and chaining algorithms. Users can verify that rows have not been tampered. A hash value that is part of the row metadata is used to chain and validate rows.

Blockchain tables enable you to implement a centralized ledger model where all participants in the blockchain network have access to the same tamper-resistant ledger.

A centralized ledger model reduces administrative overheads of setting up a decentralized ledger network, leads to a relatively lower latency compared to decentralized ledgers, enhances developer productivity, reduces the time to market, and leads to significant savings for the organization. Database users can continue to use the same tools and practices that they would use for other database application development.

See Managing Blockchain Tables for more information.

JSON Document Store Enhancements

• Enhancements to Data Guide

  Enhances development flexibility and allows for materialized views, which may improve query performance with a trade-off against DML performance.

  • JSON_DATAGUIDE now gathers statistic information if you specify DBMS_JSON.GATHER_STATS in the third argument. They are computed dynamically (up-to-date) at the time of the function call.

  • DBMS_JSON.CREATE_VIEW now gives you the option to create a materialized view instead of a standard view. It also gives you the option to specify a particular path so the view can be created on a subset of the data. Both CREATE_VIEW and ADD_VIRTUAL_COLUMN are enhanced to allow automatic resolution of column naming conflicts, to provide a prefix to be applied to column names, and to specify the case-sensitivity of column names.

  See JSON Data Guide for more information.

• Multivalue Index for JSON DataType

  A new create index syntax CREATE MULTIVALUE INDEX allows you to create a functional index on arrays of strings or numbers within a JSON datatype column. Each unique value within the array will become a searchable index entry. This avoids the need for full JSON scans to find values within arrays in JSON columns, when searched using the JSON_EXISTS or JSON_VALUE operators. It provides similar benefits to conventional functional indexes when searching JSON, but conventional functional indexes are limited to a single indexed value per row.

  See Creating Multivalue Function-Based Indexes for JSON_EXISTS and Using a Multivalue Function-Based Index for more information.

• New JSON Data Type

  JSON is a new SQL and PL/SQL data type for JSON data. Using this type provides a substantial increase in query and update performance. JSON data type uses binary format OSON that is optimized for SQL/JSON query and DML processing. Using the binary format can yield database performance improvements for processing JSON data.

  You can use JSON data type and its instances in most places where a SQL data type is allowed, including:

  • As the column type for table or view DDL
  • With SQL/JSON functions and conditions, and with PL/SQL procedures and functions
  • In Oracle dot-notation query syntax
  • For creation of functional and search indexes

  Oracle Call Interface and Java Database Connectivity (JDBC) clients now provide APIs that can work directly with binary JSON datatype OSON format, significantly saving network costs and server CPU cycles. Going forward, Oracle recommends using JSON datatype to store and process JSON data.

  The Oracle Autonomous JSON Database uses OSON format to store and process JSON data.

  See Creating a Table With a JSON Column for more information.

• New Oracle SQL Function JSON_TRANSFORM

  You can use SQL function JSON_TRANSFORM to update parts of a JSON document. You specify which parts to modify, the modifications, and any new values. JSON_TRANSFORM is optimized by doing partial updates at OSON format level to achieve better JSON datatype update performance.

  JSON_TRANSFORM makes it easier for an application to modify a JSON document, without having to parse and rebuild it. In most cases, it also avoids a round-trip between the server and client for the whole document.

  See Oracle SQL Function JSON_TRANSFORM for more information.

• SQL/JSON Syntax Improvements

  You can now express more complex SQL/JSON queries and express some queries more succinctly:

  • New SQL function JSON_SCALAR accepts a scalar instance of a SQL data type and returns a scalar JSON value as an instance of JSON data type.

  • New JSON path-language item methods support JSON_SCALAR: float(), double(), binary(), ymInterval(), and dsInterval().

  • The JSON path-language and dot-notation syntax support the aggregate item methods: avg(), count(), minNumber(), maxNumber(), minString(), maxString(), sum().

    See Simple Dot-Notation Access to JSON Data and SQL/JSON Path Expression Item Methods for more information.

SODA Enhancements: New JSON Data Type

The default collection storage changes to the JSON data type. See Creating a Document Collection with SODA for PL/SQL for more information.

PL/SQL Enhancements

• PL/SQL is enhanced to help you program iteration controls using new iterators in loops and in qualified expressions.

  The new iterator constructs are clear, simple, understandable, and efficient.

  See PL/SQL Extended Iterators for more information.

Gradual Database Password Rollover for Applications

An application can change its database passwords without an administrator having to schedule downtime.

To accomplish this, a database administrator can associate a profile having a non-zero limit for the PASSWORD_ROLLOVER_TIME password profile parameter, with an application schema. This allows the database password of the application user to be altered while allowing the older password to remain valid for the time specified by the PASSWORD_ROLLOVER_TIME limit. During the rollover period of time, the application instance can use either the old password or the new password to connect to the database server. When the rollover time expires, only the new password is allowed.

In addition to the clause PASSWORD_ROLLOVER_TIME in the CREATE PROFILE and ALTER PROFILE statements, the ALTER USER statement has a clause, EXPIRE PASSWORD ROLLOVER PERIOD. The ACCOUNT_STATUS column of the DBA_USERS and USER_USERS data dictionary views have several statuses indicating values to indicate rollover status.

See Managing Gradual Database Password Rollover for Applications for more information.

• Automatic Materialized Views

• Autonomous Database only supports Cloud Links when your database version is Oracle Database 19c. Cloud Links are not supported with database version Oracle Database 21c. See Use Cloud Links for Read Only Data Access on Autonomous Database for more information.