ORACLE two-Table update

The two associated update write methods and alternative methods of oralce are very different from those of ms SQL.

Sometimes it takes 5 to 10 hours to update a large table.

 

UPDATE T_BASE_SUCC_PROUSER x set x. F _ EXPECTENDDATE = (SELECT
E.F _ endtime from t_gather_effecct e where x. F _ LOTID = E.F _ LOTTERYID AND
X. F _ effecct = E.F _ effecct)
T_BASE_SUCC_PROUSER was updated for 14 hours yesterday.
Adding an index is also so slow today
The loading time for the first time is till now and nearly five hours
In addition, the IO volume remains low on EM.
The table size is 7205 mb. Rows 7758,5575
Execution Plan
Operation object type sequence row size (KB) cost time (SEC) CPU cost I/O cost
Table access full T_BASE_SUCC_PROUSER TABLE 1 77585575 1,439,576.099 169053 2029 36788100922
Table access full t_gather_regular CT Table 2 1 0.021 139 2 25736801 136

I want to update a large table in this way and the IO usage is so low. How do you write such an update statement?

 

 

1

Update t_base_succ_prouser x set X. F _ expectenddate = (select
E.f _ endtime from t_gather_effecct e where X. F _ lotid = e.f _ lotteryid and
X. F _ effecct = e.f _ effecct)
Where exists (select 1 from t_gather_regular CT e where X. F _ lotid = e.f _ lotteryid and X. F _ regular Ct = e.f _ regular CT)

 

You must add where exists (select 1 from t_gather_exact CT e where
X. F _ lotid = e.f _ lotteryid and X. F _ regular Ct = e.f _ regular CT)
If not, all data rows will be modified. Of course, each row is only modified once.

 

2

We recommend that you use the update view method.
Update (select x. F_EXPECTENDDATE, E.F _ ENDTIME
From T_BASE_SUCC_PROUSER X left join t_gather_regular ct e on X. F _ LOTID = E.F _ lotteryid and x. F _ regular Ct = E.F _ regular CT
)
Set F_EXPECTENDDATE = F_ENDTIME

 

Two tables require foreign key links

 

3. Temporary Table Method

Create table a

Select x. *, e. f_endtime

From x

Inner join e on x. f_lotid = e. f_lotid and e. id = x. id

 

Truncate table x

Insert into x select * from;