Table A id, usedspace
Table B resourceId, filesize

Condition watch Anderson, d=B.r esourceId

Eligible table B may have multiple data article may also be 0

Is to conform to the conditions of the fileSize updates to the table in table B of A usedspace field


Script before it's better when tens of thousands of data, but data on the hundreds of thousands of millions of deadlock, could you tell me how to deal with, this batch of high efficiency will be updated with what way? The small white one, write, the great god more troubles,

CodePudding user response:

With the primary key of the table A batch update, such as A 10000 lines, most updated finished immediately submitted once; Come again next, until finally;

CodePudding user response:

Good stroke business requirements

More and more data, every time is a full table updates? Or can be part of the update?
Update operation whether can consider free is on business, such as in the evening?

CodePudding user response:

Or, starting from the requirements of
What are the requirements for the frequency of the update, how long the update time,
How long will b table filesize change once,

CodePudding user response:

Update is 1 day, can in the evening, and then every day needs to be updated full table

CodePudding user response:

B table filesize changes of the time, only in the evening for a settlement

CodePudding user response:

reference 1st floor wmxcn2000 response:

with table A primary key, batch updates, such as A 10000 lines, most updated finished immediately submitted once; Come again next, until finally;

Table A primary key is A random value generated by the system, there is no any rule,
So how to batch update

CodePudding user response:

Can consider to use the ROWID from table B batch update A table value (10000) at A time

CodePudding user response:

Small white to learn

Look at the building field is the user ID and A table used space table B is user resource ID and file size

Need settlement users on the same day with every day how much space size shows that the user has to use the next day

Business logic is this?

I think the first batch group for the table B is calculated and then associated with A table to calculate reasonable

For estimate your users won't have millions of users operating record table B article millions of...

CodePudding user response:

The building Lord if use normal UPDATE UPDATE, it is suggested that do not use, can use the merge, if not the production environment can create a table to use the merge in the middle

CodePudding user response:

To build a temporary table t1, 2 tables associated results into a temporary table t1, truncate the source table, just come back from t1insert,

CodePudding user response:

Don't know how to write you this statement, consider using oracle merge grammar?

 
The merge into AA 
Using (select bb. Resourceid, bb. Filesize from bb) s 
On (aa) id=s.r esourceid) 
The when matched then 
Update the set of aa. Usedspace=s. ilesize; 
Commit; 

Millions use the merge, should not a deadlock, speed is quite quick, get a test environment with actual data under test,

CodePudding user response:

The best call lock, how can you say a deadlock, deadlocks are multiple transactions

CodePudding user response:

The efficiency of the 11 floor seems quite high