DBA Hub

📋Steps in this guide1/4

Staging Tables in Oracle Database 23ai/26ai

Oracle database 23ai/26ai 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.

oracle 23configurationintermediate
by OracleDba
30 views
1

Create Staging Tables

In the following example we create two heap tables, one of which uses the clause. The column in the views indicates is the table is a staging table. We can convert an existing heap table into a staging table using the command. We can convert it back using the clause.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
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)
);

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>

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>

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>
2

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. 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. We can't alter a staging table to add compression after it is created. Staging tables can be partitioned but we can't perform any partition maintenance operations that will result in data being compressed.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
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>

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>

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>
3

Statistics

Staging tables only use dynamic sampling, so we can't gather table statistics.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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>
4

Recycle Bin

Staging tables are not protected by the recycle bin. First we recreate the tables in their original form. We check the recycle bin, which is empty. We drop the tables without the option. We check the recycle bin, and only the normal table is present. The staging table has not been put into the recycle bin. For more information see: - CREATE TABLE Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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)
);

SQL> show recyclebin
SQL>

drop table if exists staging_tab;
drop table if exists normal_tab;

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
NORMAL_TAB       BIN$E4gmN27IIGDgZQAAAAAAAQ==$0 TABLE        2024-03-12:20:10:42
SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!