Inserting rows into worktables using APPEND (bulk load for Oracle)

Avatar

Avatar
Boost 1
Level 1
Klaster
Level 1

Like

1 like

Total Posts

5 posts

Correct reply

0 solutions
Top badges earned
Boost 1
View profile

Avatar
Boost 1
Level 1
Klaster
Level 1

Like

1 like

Total Posts

5 posts

Correct reply

0 solutions
Top badges earned
Boost 1
View profile
Klaster
Level 1

13-10-2020

Hi,

 

I wonder how we can change generated SQL statements like

INSERT INTO WKFxxxxxxx (col1,col2.....) SELECT ..........

into

INSERT /*+ append */ INTO WKFxxxxxxx (col1,col2.....) SELECT ..........

 

which is in fact bulk insert into table (Oracle database) - and it's greatly reduce redo logs size.

 

Any clues ?

 

 

Accepted Solutions (0)

Answers (1)

Answers (1)

Avatar

Avatar
Contributor
MVP
Milan_Vucetic
MVP

Likes

166 likes

Total Posts

384 posts

Correct reply

135 solutions
Top badges earned
Contributor
Springboard
Shape 1
Validate 25
Validate 10
View profile

Avatar
Contributor
MVP
Milan_Vucetic
MVP

Likes

166 likes

Total Posts

384 posts

Correct reply

135 solutions
Top badges earned
Contributor
Springboard
Shape 1
Validate 25
Validate 10
View profile
Milan_Vucetic
MVP

15-10-2020

Hi @Klaster ,

This is nice idea as direct-path insert can perform significantly better than conventional insert.

However, there are some constraints here:

  • Data is written directly into datafiles, bypassing the buffer cache;
  • Free space in the table is not reused;
  • Rreferential integrity constraints are ignored.

 

Regards,

Milan