Thanks for the question, NC.

Asked: June 13, 2003 - 8:27 am UTC

Last updated: June 16, 2003 - 9:30 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi

I would like to know what would be the best way to perform the following inserts.

I have tables t1, t2, t3 and x1, x2 and x3

t1 has approx 400,000 rows
t2 has approx 1,000,000 rows
t3 has approx 200,000 rows

x1,x2,x3 will star empty and be appended to each month.

currently, I have 3 nested FOR LOOPS.
The main outer loop which selects all records from t1 and
inserts into x1.
The primary key on x1 is from a sequence via a pre-insert trigger.
This sequence is returned into a variable via a returning into clase

The next inner loop selects all records from t2 where the id (primary key) from x1 matches column id in t2
and inserts into x2 (again generating a primary key via a trigger and returning it into a variable)
Also the sequence from the previous loop is inserted into x2 so that the link between x1 and x2 is created.

The last loop is much the same, insert into x3 all records from t3 where the id (primary key) from x2 matches id in t3
and inserts into x3, and the sequence from the previous loop is inserted into x3 so that the link between
x2 and x3 is created.

So we have

cursor c1 is select * from t1;
cursor c2(v_id1 in number) is select * from t2 where t2.id = v_id1;
cursor c3(v_id2 in number) is select * from t3 where t3.id = v_id2;

for rec1 in c1
loop
insert into x1 (col1, col2, col3) values (null, rec1.a, rec1,b) returning col1 into v_new_id;

for rec2 in c2(rec1.a)
loop
insert into x2 (col1, col2, col3) values (null, v_new_id, rec2.a) returning col1 into v_new_id2;

for rec3 in c3(rec2.a)
loop
insert into x3 (col1, col2, col3) values (null, v_new_id2, rec3.a);
end loop;

end loop;

end loop;

Would it be better to use arrays and bulk collect / for all insert ?
I would appreciate your advice.

Than

and Tom said...

Oh, is there a better way.

Believe it or not, that process should be nothing more then 3 insert into statements. The speedup you'll see will amaze you. The reduction in resources will astound you. I setup t1,t2,t3 like this:

ops$tkyte@ORA920> create table t1( id int, data char(20) );
Table created.

ops$tkyte@ORA920> create table t2( id int, fk_t1 int, data char(20) );
Table created.

ops$tkyte@ORA920> create table t3( id int, fk_t2 int, data char(20) );
Table created.

ops$tkyte@ORA920> insert into t1 select rownum, 'x' from big_table.big_table where rownum <= 400000;

400000 rows created.

ops$tkyte@ORA920> insert into t2
2 select rownum, id, 'x'
3 from ( select id from t1
4 union all
5 select id from t1
6 union all
7 select id from t1 where mod(id,2) = 0 );
1000000 rows created.

ops$tkyte@ORA920> insert into t3
2 select rownum, id, 'x'
3 from (select id from t2 where mod(id,5) = 0);
200000 rows created.

ops$tkyte@ORA920> create index t2_fk_idx on t2(fk_t1);
Index created.

ops$tkyte@ORA920> create index t3_fk_idx on t3(fk_t2);
Index created.

ops$tkyte@ORA920> analyze table t1 compute statistics for table for all indexes for all indexed columns;
Table analyzed.

ops$tkyte@ORA920> analyze table t2 compute statistics for table for all indexes for all indexed columns;
Table analyzed.

ops$tkyte@ORA920> analyze table t3 compute statistics for table for all indexes for all indexed columns;
Table analyzed.

then, I created your X1, x2, x3 tables

ops$tkyte@ORA920> create table x1a ( newid int, oldid int, data char(20) );
Table created.

ops$tkyte@ORA920> create table x2a ( newid int, oldid int, fk_t1 int, data char(20) );
Table created.

ops$tkyte@ORA920> create table x3a ( newid int, oldid int, fk_t2 int, data char(20) );
Table created.

ops$tkyte@ORA920> create index x1a_idx on x1a(newid,oldid);
Index created.

ops$tkyte@ORA920> create index x2a_idx on x2a(newid,oldid);
Index created.

I had x1 and x1a, x2, x2a and so on - only x1a...x2a had indexes, for my approach

On x1, x2, x3, we created triggers:

ops$tkyte@ORA920> create or replace trigger x1_trigger
2 before insert on x1 for each row
3 begin
4 select s.nextval into :new.newid from dual;
5 end;
6 /
Trigger created.

which is the worst way to approach this -- using a trigger. anyway. Here is my approach to loading x1, x2, x3:

ops$tkyte@ORA920> exec runstats_pkg.rs_start

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> alter sequence S cache 1000000;
Sequence altered.

that is someting you want to do before using a sequence really hard and heavy, you would be AMAZED at the benefits of that

ops$tkyte@ORA920>
ops$tkyte@ORA920> column S new_val S;
ops$tkyte@ORA920> select s.nextval S from dual;

S
----------
1

ops$tkyte@ORA920>
ops$tkyte@ORA920> insert /*+ APPEND */ into x1a
2 select s.nextval, id, data from t1;

400000 rows created.

ops$tkyte@ORA920> commit;

Commit complete.

ops$tkyte@ORA920>
ops$tkyte@ORA920> insert /*+ APPEND */ into x2a
2 select s.nextval, t2.id, x1.newid, t2.data
3 from t2, x1a x1
4 where x1.newid > &S
5 and x1.oldid = t2.fk_t1;
old 4: where x1.newid > &S
new 4: where x1.newid > 1

1000000 rows created.

ops$tkyte@ORA920> commit;

Commit complete.

ops$tkyte@ORA920>
ops$tkyte@ORA920> insert /*+ APPEND */ into x3a
2 select s.nextval, t3.id, x2.newid, t3.data
3 from t3, x2a x2
4 where x2.newid > &S
old 4: where x2.newid > &S
new 4: where x2.newid > 1

200000 rows created.

ops$tkyte@ORA920> commit;
Commit complete.

ops$tkyte@ORA920> alter sequence S cache 20;
Sequence altered.

ops$tkyte@ORA920> exec runstats_pkg.rs_middle;
PL/SQL procedure successfully completed.

there, that is the entire load process -- it does *everything*. Now your way:

ops$tkyte@ORA920> declare
2 l_newid_t1 int;
3 l_newid_t2 int;
4 begin
5 for x in ( select * from t1 )
6 loop
7 insert into x1 ( oldid, data ) values ( x.id, x.data )
8 returning newid into l_newid_t1;
9 for y in ( select * from t2 where t2.fk_t1 = x.id )
10 loop
11 insert into x2 ( oldid, fk_t1, data ) values ( y.id, l_newid_t1, y.data )
12 return newid into l_newid_t2;
13 for z in ( select * from t3 where t3.fk_t2 = y.id )
14 loop
15 insert into x3 ( oldid, fk_t2, data ) values ( z.id, l_newid_t2, z.data );
16 end loop;
17 end loop;
18 end loop;
19 end;
20 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> exec runstats_pkg.rs_stop(100000);
Run1 ran in 12183 hsecs
Run2 ran in 235576 hsecs
run 1 ran in 5.17% of the time

1/20th of the runtime, 20times faster (and it had two indexes extra to take care of!). But more importantly:

Name Run1 Run2 Diff
LATCH.checkpoint queue latch 36,430 141,104 104,674
LATCH.row cache enqueue latch 3,846 163,180 159,334
LATCH.dml lock allocation 370 160,329 159,959
LATCH.sequence cache 4,800,009 4,960,014 160,005
STAT...recursive cpu usage 63 199,915 199,852
STAT...CPU used by this sessio 5,509 217,812 212,303
STAT...CPU used when call star 5,509 217,812 212,303
STAT...Elapsed Time 12,270 235,598 223,328
LATCH.undo global data 19,681 253,812 234,131
STAT...enqueue releases 5,151 242,495 237,344
STAT...enqueue requests 5,151 242,499 237,348
LATCH.library cache pin alloca 2,989 323,616 320,627
LATCH.enqueue hash chains 10,545 487,183 476,638
LATCH.row cache objects 5,286 484,151 478,865
STAT...sorts (rows) 1,402,631 2,618 -1,400,013
STAT...consistent gets - exami 407,293 1,890,245 1,482,952
STAT...table scan rows gotten 400,001 2,000,000 1,599,999
STAT...table scans (short tabl 1 1,600,000 1,599,999
STAT...table scan blocks gotte 1,757 2,000,048 1,998,291
STAT...redo entries 52,903 2,215,021 2,162,118
LATCH.redo allocation 53,998 2,222,013 2,168,015
LATCH.session allocation 31 2,401,734 2,401,703
STAT...buffer is pinned count 2,600,058 0 -2,600,058
STAT...db block gets 102,375 3,161,828 3,059,453
STAT...no work - consistent re 1,207,558 4,402,253 3,194,695
STAT...db block changes 99,734 4,401,587 4,301,853
STAT...buffer is not pinned co 1,202,995 5,598,153 4,395,158
STAT...execute count 502 4,680,368 4,679,866
STAT...recursive calls 5,033 7,323,453 7,318,420
STAT...consistent gets 3,022,880 10,894,618 7,871,738
STAT...calls to get snapshot s 12,743 8,043,676 8,030,933
LATCH.shared pool 1,604,008 9,805,723 8,201,715
STAT...session logical reads 3,125,255 14,056,446 10,931,191
LATCH.library cache pin 3,204,928 16,245,960 13,041,032
LATCH.library cache 4,807,752 19,774,939 14,967,187
LATCH.cache buffers chains 6,112,094 33,752,275 27,640,181
STAT...redo size 127,584,104 606,190,928 478,606,824

I'm just showing things that were more then 100,000 different here. Look at the REDO SIZE, look at the latching differences! These are major league different...

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
20,739,779 91,534,813 70,795,034 22.66%

PL/SQL procedure successfully completed.

just use 3 inserts and be done with it. anytime you find yourself writing code -- stop -- and ask "why, why am I doing that"

Rating

(8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Two questions

A reader, June 13, 2003 - 2:01 pm UTC

Tom,

I have two questions regarding your insert statements.

"ops$tkyte@ORA920> insert /*+ APPEND */ into x2a
2 select s.nextval, t2.id, x1.newid, t2.data
3 from t2, x1a x1
4 where x1.newid > &S
5 and x1.oldid = t2.fk_t1;
old 4: where x1.newid > &S
new 4: where x1.newid > 1

1000000 rows created.

ops$tkyte@ORA920> commit;

Commit complete.

ops$tkyte@ORA920>
ops$tkyte@ORA920> insert /*+ APPEND */ into x3a
2 select s.nextval, t3.id, x2.newid, t3.data
3 from t3, x2a x2
4 where x2.newid > &S
old 4: where x2.newid > &S
new 4: where x2.newid "

Q1: Why do you need "x1.newid > &S" in the where clause when inserting into x2a?

Q2: How come the where clause "x2.oldid = t3.fk_t2;" is missing when inserting into x3a? But there is a similar condition "x1.oldid = t2.fk_t1;" when inserting into x2a?

Thanks

June 13, 2003 - 2:30 pm UTC

q1) i assumed this table X1 was an aggregate over time and hence X1 would contain last months data as well as this new month of data and that x1.oldid would be duplicated over and over.

x1.newid limits our view of X1 to just the newly added data.

q2) excellent eyeballs.

it was a cut and paste error (boundary value condition :)

ops$tkyte@ORA920> insert /*+ APPEND */ into x3a
  2  select s.nextval, t3.id, x2.newid, t3.data
  3    from t3, x2a x2
  4   where x2.newid > &S
  5     and x2.oldid = t3.fk_t2;
old   4:  where x2.newid > &S
new   4:  where x2.newid >          1

200000 rows created.



is the correct one -- notice the ';' or '/' is missing in the original -- i lost that one line.

Great example

Peter Tran, June 13, 2003 - 3:14 pm UTC

Tom,

Are you using a direct-path insert approach here?

You can do a little better if you disable logging uring the direct-path insert, however you would need to run a backup after the job completes otherwise, you run the risk of losing everything if the database crashes. Am I correct?

Thanks,
-Peter

June 13, 2003 - 3:47 pm UTC

yes, yes and yes.

Thank you

NC, June 15, 2003 - 7:47 am UTC

I kinda thought I was missing something !
But if this needs to be called from a package, what
would need to change from your example, as I dont really understand the &S thing in the where clause ?

Thanks for your help

June 15, 2003 - 9:46 am UTC

&S was the value of the sequence before we began the mass inserts.

In a plsql routine, it would look like:

select s.nextval into l_lo_seq from dual;

insert /*+ APPEND */ into x2a
select s.nextval, t2.id, x1.newid, t2.data
from t2, x1a x1
where x1.newid > L_LO_SEQ
and x1.oldid = t2.fk_t1;

Package runstats_pkg

Kurt, June 16, 2003 - 5:48 am UTC

Hi Tom,
is it possible to get the sources of the package runstats_pkg?

June 16, 2003 - 8:11 am UTC

</code> http://asktom.oracle.com/~tkyte/ <code>

look for the simple test harness.

insert processing

Santhanam, June 16, 2003 - 9:30 am UTC

Very useful

Better way for query

Anne, February 17, 2006 - 12:00 pm UTC

Hi Tom,

I would appreciate your advise on whether my query can be further refined/optimized.

create table run_history
(
run_id number(15) not null,
run_date date not null,
end_control_id number(15) not null,
start_control_id number (15)
)

--Master tables for controls
create table control_history
(
control_id number(15) not null,
create_date date not null,
comments varchar2(50)
)

I have a proc that runs for a set of control_ids and when done, inserts the last processed control_id into the run_history table. When it runs next it takes the max(end_control_id) from run_history table and starts processing from the "next" control_id in the control_history table
Objective :
Query to select the max(end_control_id) from run_history table and the "next" control_id from the control_history table as one record.

select *
from
(
select
prev_control.*
, ch.*
from control_history ch
, ( select
*
from (
select rh.*
from run_history rh
order by run_id desc
)
where rownum = 1
) prev_control
where ch.control_id > prev_control.end_control_id
order by ch.control_id asc
)
where rownum = 1
;

Thanks!

Can this query be refined

Anne, February 17, 2006 - 12:01 pm UTC

APPEND with NOLOGGING

Ravi B, September 26, 2013 - 4:04 pm UTC

Hi Tom,

Does APPEND hint needs NOLOGGING clause to do direct path insert? In other words is there any difference if we say

INSERT /*+APPEND*/ into TEST_TABLE SELECT * FROM ..

Vs

INSERT /*+APPEND*/ into TEST_TABLE NOLOGGING SELECT * FROM ..

Thanks!

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Two questions

Great example

Thank you

Package runstats_pkg

insert processing

Better way for query

Can this query be refined

APPEND with NOLOGGING

More to Explore

PL/SQL demos

PL/SQL docs

Is there a better way to do this insert

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Two questions

Great example

Thank you

Package runstats_pkg

insert processing

Better way for query

Can this query be refined

APPEND with NOLOGGING

More to Explore

PL/SQL demos

PL/SQL docs