Staging Tables in Oracle 23ai

 

As you may know, using staging tables is a common practice in Data Warehouse environments, primarily during the data loading process into the Warehouse Database. Typically, raw data received from source databases is first loaded into a temporary area, referred to as staging tables. After cleansing inconsistent and invalid data and transforming it into a standard format, the cleaned data is stored in the final operational tables.

In addition to initial data loading, staging tables are also used for updates and are often truncated before reuse.

In Oracle 23ai, a new minor feature has been introduced for this purpose, allowing users to create tables specifically designed as staging tables. This is achieved by adding the FOR STAGING clause to the CREATE TABLE statement.

Creating Staging Tables

Using the FOR STAGING clause, staging tables can be created as follows:

SQL> create table STGtbl (id number,name varchar2(100),address varchar2(1000)) FOR STAGING;
Table created.

Identifying Staging Tables

The DBA_TABLES view now includes a new STAGING column, which allows filtering for staging tables:

SQL> select table_name,staging from dba_tables where staging='YES';
TABLE_NAME STAGING
----------            -----------
STGTBL                     YES

Enabling/Disabling Staging for a Table

You can disable the staging feature for a table as follows:

SQL> alter table STGtbl not for staging;
Table altered.

SQL> select table_name,staging from dba_tables where staging='YES';
no rows selected

Compression and Staging Tables

Compression is not supported for staging tables:

SQL> alter table STGtbl compress;
ORA-38500: Invalid operation on Staging Table

Even if COMPRESS is specified during creation, it is ignored:

SQL> create table STGtbl (id number,name varchar2(100),address varchar2(1000)) FOR STAGING COMPRESS;
Table created.
SQL> select table_name,staging,compression from dba_tables where staging='YES';
TABLE_NAME STA COMPRESS
---------- --- --------
STGTBL         YES DISABLED

Partitioning Staging Tables

Staging tables can also be partitioned. For instance:

SQL>create table stgTBL
(
  id number,
  name varchar2(100),
  mydate    DATE
)
partition by range ( mydate )
interval ( numtoyminterval(1,'MONTH'))
(
 PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY'))
) 
for staging;
Table created.

SQL> insert into stgTBL values(1,'test1',sysdate);
1 row created.
SQL> insert into stgTBL values(1,'test1',sysdate-400);
1 row created.
SQL> insert into stgTBL values(1,'test1',sysdate-800);
1 row created.
SQL> commit;
Commit complete.

SQL> select TABLE_NAME,PARTITION_NAME from dba_tab_partitions where TABLE_NAME='STGTBL';
TABLE_NAME PARTITION_NAME
---------- --------------
STGTBL     P1
STGTBL     SYS_P471
STGTBL     SYS_P472
STGTBL     SYS_P473

Inserting data automatically creates appropriate partitions:

INSERT INTO STGtbl VALUES (1, 'test1', SYSDATE);
INSERT INTO STGtbl VALUES (1, 'test1', SYSDATE - 400);
INSERT INTO STGtbl VALUES (1, 'test1', SYSDATE - 800);
COMMIT;

Partitions can be managed like any other partitioned table:

SQL> alter table STGTBL drop partition SYS_P471;
Table altered.

Statistics on Staging Tables

Statistics for staging tables are locked by default and cannot be manually updated unless explicitly unlocked:

SQL> exec dbms_stats.gather_table_stats(null,’STGTBL’);
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “SYS.DBMS_STATS”, line 42661
ORA-06512: at “SYS.DBMS_STATS”, line 41946
ORA-06512: at “SYS.DBMS_STATS”, line 9213
ORA-06512: at “SYS.DBMS_STATS”, line 10277
ORA-06512: at “SYS.DBMS_STATS”, line 41094
ORA-06512: at “SYS.DBMS_STATS”, line 42094
ORA-06512: at “SYS.DBMS_STATS”, line 42642
ORA-06512: at line 1

Recycle Bin Behavior

Staging tables are not moved to the Recycle Bin upon deletion, even when the Recycle Bin feature is enabled:

SQL> show parameter recyclebin
NAME                                 TYPE        VALUE
---------------------    ----------- ------------------------
recyclebin                           string      on

SQL> drop table STGTBL;
Table dropped.

SQL> select * from dba_recyclebin;
no rows selected