DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

Oracle Database 23ai Fast Ingest Enhancements

#linux #sql #database #devops

The Memoptimized Rowstore Fast Ingest feature, introduced in Oracle Database 19c, is highly beneficial for IoT applications. With Oracle Database 23ai, several enhancements have been introduced, which are the focus of this document.

Partitioning Support

In Oracle Database 19c and 21c, it was not possible to enable MEMOPTIMIZE FOR WRITE on partitioned tables:

Example (Oracle Database 21c):

Connected to Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 
SQL> create table TBL1
  2  (
  3    id   NUMBER not null,
  4    text VARCHAR2(3000),
  5    mydate date
  6  )
  7  SEGMENT CREATION IMMEDIATE
  8  partition by range ( mydate )
  9  interval ( numtoyminterval(1,'MONTH'))
 10  (
 11   PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2015', 'DD-MM-YYYY'))
 12  );
Table created
Enter fullscreen mode Exit fullscreen mode 
SQL> ALTER TABLE tbl1 MEMOPTIMIZE FOR WRITE;
ORA-62165: MEMOPTIMIZE FOR WRITE cannot be enabled on table with specified partitioning type.
Enter fullscreen mode Exit fullscreen mode

In Oracle Database 23ai, this limitation has been removed:

Example (Oracle Database 23ai):

Connected to Oracle Database 23c Free, Release 23.0.0.0.0 
SQL> create table TBL1
  2  (
  3    id   NUMBER not null,
  4    text VARCHAR2(3000),
  5    mydate date
  6  )
  7  SEGMENT CREATION IMMEDIATE
  8  partition by range ( mydate )
  9  interval ( numtoyminterval(1,'MONTH'))
 10  (
 11   PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2015', 'DD-MM-YYYY'))
 12  );
Table created
Enter fullscreen mode Exit fullscreen mode

Compression Support

In Oracle Database 19c and 21c, the MEMOPTIMIZE FOR WRITE feature was incompatible with compressed tables:

Example (Oracle Database 21c):

Connected to Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 
SQL> create table TBL1
  2  (
  3    id   NUMBER not null,
  4    text VARCHAR2(3000)
  5  )
  6  SEGMENT CREATION IMMEDIATE
  7  MEMOPTIMIZE FOR WRITE
  8  COMPRESS;
ORA-62146: MEMOPTIMIZE FOR WRITE feature cannot be used with COMPRESS option.
Enter fullscreen mode Exit fullscreen mode

This restriction has been addressed in Oracle Database 23ai:

Example (Oracle Database 23ai):

Connected to Oracle Database 23c Free, Release 23.0.0.0.0 
SQL> create table TBL1
  2  (
  3    id   NUMBER not null,
  4    text VARCHAR2(3000)
  5  )
  6  SEGMENT CREATION IMMEDIATE
  7  MEMOPTIMIZE FOR WRITE
  8  COMPRESS;
Table created
Enter fullscreen mode Exit fullscreen mode

LOB Support

In Oracle Database 19c, the Fast Ingest feature could not be enabled for tables with LOBs:

Example (Oracle Database 19c):

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 19 15:36:02 2023
SQL> create table usef.TBL1
  2  (
  3    id   NUMBER not null,
  4    text VARCHAR2(3000),
  5    PIC BLOB
  6  )
  7  SEGMENT CREATION IMMEDIATE
  8  MEMOPTIMIZE FOR WRITE;
ORA-62152: MEMOPTIMIZE FOR WRITE feature cannot be enabled on table with LOBs.
Enter fullscreen mode Exit fullscreen mode

With Oracle Database 23ai, LOB support has been introduced:

Example (Oracle Database 23ai):

Connected to Oracle Database 23c Free, Release 23.0.0.0.0 
SQL> create table usef.TBL1
  2  (
  3    id   NUMBER not null,
  4    text VARCHAR2(3000),
  5    PIC BLOB
  6  )
  7  SEGMENT CREATION IMMEDIATE
  8  MEMOPTIMIZE FOR WRITE;
Table created
Enter fullscreen mode Exit fullscreen mode

These enhancements in Oracle Database 23ai significantly improve the usability and flexibility of the Memoptimized Rowstore Fast Ingest feature, making it more suitable for diverse and complex workloads.

Top comments (0)