8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Staging Tables in Oracle Database 23c
When loading data into the database we often use staging tables. We may load the data into one or more staging tables, then transform it before loading it into the destination tables. As a result, the data in these staging tables is volatile.
Oracle database 23c introduces the FOR STAGING clause in the CREATE TABLE command to create a variation of heap tables, which have optimal configuration for fast data ingestion.
Create Staging Tables
In the following example we create two heap tables, one of which uses the FOR STAGING clause.
drop table if exists staging_tab purge; drop table if exists normal_tab purge; create table staging_tab ( id number generated always as identity, data1 varchar2(100), data2 varchar2(100), data3 varchar2(100), data4 varchar2(100) ) for staging; create table normal_tab ( id number generated always as identity, data1 varchar2(100), data2 varchar2(100), data3 varchar2(100), data4 varchar2(100) );
The STAGING column in the {USER|ALL|DBA|CDB}_TABLES views indicates is the table is a staging table.
column table_name format a30
column staging format a10
select table_name,
staging
from user_tables
where table_name like '%TAB';
TABLE_NAME STAGING
------------------------------ ----------
NORMAL_TAB NO
STAGING_TAB YES
SQL>
We can convert an existing heap table into a staging table using the ALTER TABLE command.
alter table normal_tab for staging;
select table_name,
staging
from user_tables
where table_name like '%TAB';
TABLE_NAME STAGING
------------------------------ ----------
NORMAL_TAB YES
STAGING_TAB YES
SQL>
We can convert it back using the NOT FOR STAGING clause.
alter table normal_tab not for staging;
select table_name,
staging
from user_tables
where table_name like '%TAB';
TABLE_NAME STAGING
------------------------------ ----------
NORMAL_TAB NO
STAGING_TAB YES
SQL>
Compression
Compression is disabled for staging tables. We can create a staging table with a compression clause, but compression will still be disabled for loads.
drop table if exists staging_tab purge;
drop table if exists normal_tab purge;
create table staging_tab (
id number generated always as identity,
data1 varchar2(100),
data2 varchar2(100),
data3 varchar2(100),
data4 varchar2(100)
)
for staging
compress;
create table normal_tab (
id number generated always as identity,
data1 varchar2(100),
data2 varchar2(100),
data3 varchar2(100),
data4 varchar2(100)
)
compress;
select table_name,
staging,
compression
from user_tables
where table_name like '%TAB';
TABLE_NAME STAGING COMPRESS
------------------------------ ---------- --------
NORMAL_TAB NO ENABLED
STAGING_TAB YES DISABLED
SQL>
An existing table containing compressed data can be switched to a staging table, but future inserts will not be compressed. In the following example we insert some data into the normal table, which has compression enabled. We then switch it to a staging table.
insert into normal_tab (data1, data2, data3, data4)
select 'data1 value', 'data1 value', 'data1 value', 'data1 value'
from dual
connect by level <= 1000;
alter table normal_tab for staging;
select table_name,
staging,
compression
from user_tables
where table_name like '%TAB';
TABLE_NAME STAGING COMPRESS
------------------------------ ---------- --------
NORMAL_TAB YES DISABLED
STAGING_TAB YES DISABLED
SQL>
We can't alter a staging table to add compression after it is created.
drop table if exists staging_tab purge; create table staging_tab ( id number generated always as identity, data1 varchar2(100), data2 varchar2(100), data3 varchar2(100), data4 varchar2(100) ) for staging; alter table staging_tab compress; alter table staging_tab compress * ERROR at line 1: ORA-38500: Invalid operation on Staging Table Help: https://docs.oracle.com/error-help/db/ora-38500/ SQL>
Staging tables can be partitioned but we can't perform any partition maintenance operations that will result in data being compressed.
Statistics
Staging tables only use dynamic sampling, so we can't gather table statistics.
exec dbms_stats.gather_table_stats(null,'STAGING_TABLE'); BEGIN dbms_stats.gather_table_stats(null,'STAGING_TABLE'); END; * ERROR at line 1: ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: at "SYS.DBMS_STATS", line 42112 ORA-06512: at "SYS.DBMS_STATS", line 41397 ORA-06512: at "SYS.DBMS_STATS", line 9071 ORA-06512: at "SYS.DBMS_STATS", line 10135 ORA-06512: at "SYS.DBMS_STATS", line 40597 ORA-06512: at "SYS.DBMS_STATS", line 41545 ORA-06512: at "SYS.DBMS_STATS", line 42093 ORA-06512: at line 1 Help: https://docs.oracle.com/error-help/db/ora-20005/ SQL>
Recycle Bin
Staging tables are not protected by the recycle bin.
First we recreate the tables in their original form.
drop table if exists staging_tab purge; drop table if exists normal_tab purge; create table staging_tab ( id number generated always as identity, data1 varchar2(100), data2 varchar2(100), data3 varchar2(100), data4 varchar2(100) ) for staging; create table normal_tab ( id number generated always as identity, data1 varchar2(100), data2 varchar2(100), data3 varchar2(100), data4 varchar2(100) );
We check the recycle bin, which is empty.
SQL> show recyclebin SQL>
We drop the tables without the PURGE option.
drop table if exists staging_tab; drop table if exists normal_tab;
We check the recycle bin, and only the normal table is present. The staging table has not been put into the recycle bin.
SQL> show recyclebin ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- NORMAL_TAB BIN$E4gmN27IIGDgZQAAAAAAAQ==$0 TABLE 2024-03-12:20:10:42 SQL>
For more information see:
Hope this helps. Regards Tim...