The code is as follows:
The MERGE INTO T_PA_CITEM_KIND t_pa
USING CCIC. PRPCITEMKIND t_p
ON (t_pa. POLICYNO=t_p. POLICYNO
AND t_pa. SEQNO=t_p. ITEMKINDNO)
The WHEN MATCHED THEN
The UPDATE
The SET t_pa. RISKCODE=t_p. RISKCODE
, t_pa. CLAUSENAME=t_p. KINDNAME
, t_pa. KINDNAME=t_p. ItemDetailName
, t_pa. STARTDATE=t_p. STARTDATE
, t_pa. STARTHOUR=t_p. STARTHOUR
T_pa), ENDDATE=t_p, ENDDATE
, t_pa. ENDHOUR=t_p. ENDHOUR
, t_pa. CURRENCY=t_p. CURRENCY
, t_pa. CALCULATEFLAG=t_p. CALCULATEFLAG
, t_pa. UNITAMOUNT=t_p. UNITAMOUNT
, t_pa. QUANTITY=t_p. QUANTITY
, t_pa. RATE=t_p. RATE
, t_pa. SHORTRATE=t_p. SHORTRATE
, t_pa. SHORTRATEFLAG=t_p. SHORTRATEFLAG
, t_pa. AMOUNT=t_p. AMOUNT
, t_pa. PREMIUM=t_p. PREMIUM
, t_pa. KINDVAT=t_p. KINDVAT
, t_pa. TNIPREMIUM=t_p. TNIPREMIUM
, t_pa. VATRATETYPE=t_p. VATRATETYPE
, t_pa. FLAG=t_p. FLAG
WHEN NOT MATCHED THEN
INSERT (POLICYNO, SEQNO, ITEMTYPE, REL_REF_SEQNO, RISKCODE, CLAUSECODE, CLAUSENAME, KINDCODE
KINDNAME, STARTDATE, ENDDATE, STARTHOUR, ENDHOUR, CURRENCY, CALCULATEFLAG, UNITAMOUNT
, UNITPREMIUM, QUANTITY, RATE, SHORTRATE SHORTRATEFLAG, AMOUNT of PREMIUM, KINDVAT
, TNIPREMIUM VATRATETYPE, FLAG, ORIGININPUTFLAG)
VALUES (t_p POLICYNO
T_p. ITEMKINDNO
The NULL
The NULL
T_p. RISKCODE
The null
T_p. KINDNAME
The null
T_p. ItemDetailName
T_p. STARTDATE
T_p. STARTHOUR
T_p. ENDDATE
T_p. ENDHOUR
T_p. CURRENCY
T_p. CALCULATEFLAG
T_p. UNITAMOUNT
The NULL
T_p. QUANTITY
T_p. RATE
T_p. SHORTRATE
T_p. SHORTRATEFLAG
T_p. AMOUNT
T_p. PREMIUM
T_p. KINDVAT
T_p. TNIPREMIUM
T_p. VATRATETYPE
T_p. FLAG
,
'Y')
Execution plan is as follows:


The two tables data volume is more than 40 million, update, and insert the amount of data is quite large, the actual implementation process there is a time mark, no, so I didn't add,

Execution time is too long, can you tell me what's the optimized method, both have a primary key, relevance is associated with a primary key,
Online, etc., thank you!

CodePudding user response:

If you use the associated field is target table's primary key field , then there may be several improvement measures:
1, look at the two table join field types inconsistent? If not consistent, then into a temporary table field type consistent with the target table, or modify the table definition, or use the corresponding function to modify a temporary table join condition;
2, if not 1, you can try two table statistics collection: exec dbms_stats. Gather_table_stats (' user name 'and' table ', cascade=& gt; True, method_opt=& gt; 'for all indexed columns repeat')
3, if after the above adjustment is slow, unable to adjust the execution plan of the HASH JOIN into NESTED LOOPS, then add to alert, and behind the merge keyword: merge/* + use_nl_with_index (t_pa) */
4, if after 3 doesn't work, then continue to add hint after statements and execution plan

CodePudding user response:

reference 1st floor minsic78 response:

if you use the associated field is target table's primary key field , then there may be several improvement measures:
1, look at the two table join field types inconsistent? If not consistent, then into a temporary table field type consistent with the target table, or modify the table definition, or use the corresponding function to modify a temporary table join condition;
2, if not 1, you can try two table statistics collection: exec dbms_stats. Gather_table_stats (' user name 'and' table ', cascade=& gt; True, method_opt=& gt; 'for all indexed columns repeat')
3, if after the above adjustment is slow, unable to adjust the execution plan of the HASH JOIN into NESTED LOOPS, then add to alert, and behind the merge keyword: merge/* + use_nl_with_index (t_pa) */
4, if after 3 doesn't work, then continue to add hint after statements and execution plan up

add: if your temporary table 40 million data are all involved in the merge, so now the SQL slow is normal, the execution plan is reasonable, but if the actual running conditions will have time on this piece of temporary table, filter out a small number of records, so the proposal on hair is the value of implementation,

CodePudding user response:

Both 4000 w to calculate? The best batch updates, such as to try the original table once every 10 w line, look at the time,

CodePudding user response:

1. Make sure the update data have how old?
2. Update the amount of data to check if more T_PA_CITEM_KIND t_pa table index, usually to rebuilt after the index disabled, because changes will produce large amounts of log data,
3. CCIC. PRPCITEMKIND t_p add and modify policy, does not need to do the whole quantity policy data synchronization

CodePudding user response:

Brothers, if the database is like you do, tianhe will run a number sooner or later die

This must levels of data, be sure to do big affairs division, no matter what the business logic, less time will be simple, large amount of time is not easy, even a simple SQL can finish things, also can't just go and do a simple SQL

CodePudding user response:

Please try adding indexes combined index or the parallel solution

CodePudding user response:

1, t_pa POLICYNO, t_p POLICYNO, t_pa. SEQNO, t_p. ITEMKINDNO index (data type)
2, index on useless words can be specified using the index,
After 3, marge Into add/* + parallel t_pa, (5) the parallel (t_p, 5) use_hash (t_pa t_p) */this statement for concurrent statement, your CPU and memory should be enough, otherwise it may bring down your CPU, 5 can customize, corresponding decrease,
3, if the overall table data is more, segmentation, which is 10 w a, circulation,
4, in the end, the merge Into actually also can use the following ways, T_PA_CITEM_KIND table increase iscurrent (default is 1, effective),
First update T_PA_CITEM_KIND set iscurrent=0 where the exists (select 1 from PRPCITEMKIND f the where f...=t... And f...=t... )
In the insert into T_PA_CITEM_KIND (fields, iscurrent) (the select field, 1 from PRPCITEMKIND)
Every time take iscurrent=1, this is the practice of data warehouse, can retain the original data, specific to see you are using,

CodePudding user response:

This transaction must be divided into N small affairs to deal with, such as use cursors per 10000 lines to submit an update method, and can add resources permits parallel, your statement is not complicated simply complicates the table size make the transaction