Why I do the oracle database update or insert, update or insert into hundreds of thousands of soon, the article one hundred thousand to half an hour or so, more than millions more than 5 hours, if millions of article above, is a long way off, one time more than 800 ten thousand article want to one-time do insert operation, the result didn't end more than 50 hours, finally had to end,
Do seem to update or insert before tens of millions of article also soon,
Now with insert statement is:
Insert/* + append + */into mytest xl1 (dh, sj, vi) select/* + no_merge (a2) use_hash (a2) */dh, sj, sum (jj) over (order by dh, sj rows between var50 PRECEDING and current row) as the vi from mytest. Xl a2 where a2. Sj<20170000 and a2. Sj> 20160700;

The update a1 set var51=(select vi from (select/* + no_merge (a2) use_hash (a2) */dh, sj, sum (jj) over (order by dh, sj rows between var50 PRECEDING and current row) as vi
The from a2 where a2, a3) dh=a1. Dh and a1. Sj=a2. Sj);

CodePudding user response:

My server is the IBM 3850 x5, 32 gb memory, 16 nuclear CPU, hard disk raid 6, 2 t, the user table space is 96 g, 64 g temporary tablespace

CodePudding user response:

The alter table xl nologging;
Alter session set skip_unusable_indexes=true;
Insert/* + append + */into mytest xl1 (dh, sj, vi) select/* + no_merge (a2) use_hash (a2) */dh, sj, sum (jj) over (order by dh, sj rows between var50 PRECEDING and current row) as the vi from mytest. Xl a2 where a2. Sj<20170000 and a2. Sj> 20160700;
In this way, more than 200000, took 34 minutes

CodePudding user response:

The merge into a1
Using (select/* + no_merge (a2) use_hash (a2) */dh, sj, sum (jj)
Over (order by dh, sj rows between var50 PRECEDING and current row) as the vi from a1 where sj<20170000 and sj> 20160700) a2
On (a1. Dh=a2. Dh and a1. Sj=a2. Sj)
The when matched then
Update the set a1. Var51=a2. Vi.
Commit;

There are more than 700000 between 2016-2017, updated more than 1 hours haven't finished