DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

Oracle 23ai  -  Staging Table Feature

#oracle #sql #linux #database

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.
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Compression and Staging Tables
Compression is not supported for staging tables:

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

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
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode 
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.
Enter fullscreen mode Exit fullscreen mode 
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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Partitions can be managed like any other partitioned table:

SQL> alter table STGTBL drop partition SYS_P471;
Table altered.
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Vahid Yousefzadeh
Oracle Database Administrator
✉vahidusefzadeh@gmail.com
Telegram channel :https://t.me/oracledb

Top comments (0)