A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

SQL New Features in Oracle Database 19c

3 min read
SQL Macros

You can create SQL macros (SQM) to factor out common SQL expressions and statements into reusable, parameterized constructs that can be used in other SQL statements.

Starting with Oracle Database release 19c, version 19.7, SQL table macros are supported. SQL table macros are expressions, typically used in a FROM clause, to act as a sort of polymorphic (parameterized) views.

SQL table macros increase developer productivity, simplify collaborative development, and improve code quality.
Finer Granularity Supplemental Logging

Fine-grained supplemental logging provides a way for partial database replication users to disable supplemental logging for uninteresting tables even when supplemental logging is enabled at the database or schema level.

Use this feature in cases where where only some of the tables in the database require supplemental logging and thereby significantly reduce overhead of resource usage and redo generation.

To use this feature configure the new supplemental_subset_replication_clause added to the supplemental_db_logging clause of the ALTER DATABASE and ALTER PLUGGABLE DATABASE statements.
Cloud Object Store Support Using REST APIs

Oracle Data Pump can import data from files located in a supported object store.

You can specify database credentials on the command line and store default credentials in the database using the new property_clause of the ALTER DATABASE statement.
Multiple Table Family Support for System-Managed Sharding

This feature applies to system-managed sharded databases only. You can host different applications accessing different table families on one sharded database with the CREATE SHARDED TABLE statement.

You can create more than one table family with system sharding, but only one table family is supported for user-defined and composite sharding.
Generation of Unique Sequence Numbers Across Shards

You can generate globally unique sequence numbers across shards for non-primary key columns with unique constraints without having to manage them yourself. The sharded database manages these sequence numbers for you.

Use the SHARD clause of the CREATE SEQUENCE statement or the ALTER SEQUENCE statement to generate unique sequence numbers across shards.
Big Data and Performance Enhancements for In-Memory External Tables

The inmemory_clause of the CREATE TABLE and ALTER TABLE statement supports supports specification of the ORACLE_HIVE and ORACLE_BIGDATA driver types.

You can specify INMEMORY in the inmemory_clause clause on non-partitioned tables to support these driver types.
Bitmap Based Count Dictinct SQL Functions

You can use five new bit vector functions for speeding up COUNT DISTINCT operations within a SQL query:

Memoptimized Rowstore - Fast Ingest

Use the memoptimize_write_clause of CREATE TABLE or ALTER TABLE to enable fast ingest.

Fast ingest optimizes the memory processing of high frequency single row data inserts from Internet of Things (IoT) applications by using a large buffering pool to store the inserts before writing them to disk.
Hybrid Partitioned Tables

You can create hybrid partitioned tables where some partitions reside in Oracle database segments and some partitions reside in external files and sources. Internal and external partitions can be integrated into a single partitioned table as needed.

Specify INTERNAL or EXTERNAL in the table_partition_description clause of CREATE TABLE or ALTER TABLE.