update huge table every n rows [message #669922] |
Thu, 24 May 2018 10:02 |
|
laredoeneko
Messages: 21 Registered: November 2017
|
Junior Member |
|
|
hello
i have a table link this:
CREATE TABLE TEST_UPDATE
(
ELEMENT VARCHAR2(200 BYTE) NOT NULL,
EXECUTION_DATE TIMESTAMP(6) NOT NULL
);
this table have more than 45M rows, table has data for many element by day.
i wanna update in all the rows the value execution_date, p.e. update with 1 more day.
i.e
update test_update set execution_date=execution_date+4 where execution_date >(sysdate -4);
ie update values in table with execution_date=>4 days ago to "execution_date+4"
Insert into PORTALES.TEST_UPDATE
(ELEMENT, EXECUTION_DATE)
Values
('cesar-3-13-9', TO_TIMESTAMP('17/5/2018 1:05:17.000000 AM','fmDDfm/fmMMfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into PORTALES.TEST_UPDATE
(ELEMENT, EXECUTION_DATE)
Values
('cesar-3-13-9', TO_TIMESTAMP('17/5/2018 1:05:08.000000 PM','fmDDfm/fmMMfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into PORTALES.TEST_UPDATE
(ELEMENT, EXECUTION_DATE)
Values
('cesar-3-13-9', TO_TIMESTAMP('18/5/2018 1:05:09.000000 AM','fmDDfm/fmMMfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into PORTALES.TEST_UPDATE
(ELEMENT, EXECUTION_DATE)
Values
('cesar-3-13-9', TO_TIMESTAMP('18/5/2018 1:05:16.000000 PM','fmDDfm/fmMMfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into PORTALES.TEST_UPDATE
(ELEMENT, EXECUTION_DATE)
Values
('cesar-3-13-9', TO_TIMESTAMP('19/5/2018 1:05:11.000000 AM','fmDDfm/fmMMfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into PORTALES.TEST_UPDATE
(ELEMENT, EXECUTION_DATE)
Values
('cesar-3-13-9', TO_TIMESTAMP('19/5/2018 1:05:17.000000 PM','fmDDfm/fmMMfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into PORTALES.TEST_UPDATE
(ELEMENT, EXECUTION_DATE)
Values
('cesar-3-13-9', TO_TIMESTAMP('20/5/2018 1:05:14.000000 AM','fmDDfm/fmMMfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into PORTALES.TEST_UPDATE
(ELEMENT, EXECUTION_DATE)
Values
('cesar-3-13-9', TO_TIMESTAMP('20/5/2018 1:05:16.000000 PM','fmDDfm/fmMMfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into PORTALES.TEST_UPDATE
(ELEMENT, EXECUTION_DATE)
Values
('cesar-3-13-9', TO_TIMESTAMP('21/5/2018 1:05:23.000000 AM','fmDDfm/fmMMfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into PORTALES.TEST_UPDATE
(ELEMENT, EXECUTION_DATE)
Values
('cesar-3-13-9', TO_TIMESTAMP('21/5/2018 1:05:16.000000 PM','fmDDfm/fmMMfm/YYYY fmHH12fm:MI:SS.FF AM'));
COMMIT;
But i wanna update and make a commit every 1000 rows i.e. the update complete of the table in my server take more than 4 hours.
is it possible with a pl/sql update the table every 1000 rows? as i indicated my table has more than 50M register so complete update is not possible.
thank you very much for you help
Cesar
|
|
|
|
|
|
Re: update huge table every n rows [message #669927 is a reply to message #669923] |
Thu, 24 May 2018 13:06 |
|
laredoeneko
Messages: 21 Registered: November 2017
|
Junior Member |
|
|
thank you very much for you fast replay.
there is no problem if the pl takes more time than a complete update, i need have some records update as soon as possible, not the entire table.
EXECUTION_DATE column is type TIMESTAMP so why does DELETE compare it to DATE datatype & force implicit datatype conversion?
I donot know how do it....i will investigate.
post EXECUTION PLAN for existing DELETE statement
is EXECUTION_DATE column indexed?
yes, it is a pk with another row, and also there is a index in this column
[Updated on: Thu, 24 May 2018 13:08] Report message to a moderator
|
|
|
Re: update huge table every n rows [message #669928 is a reply to message #669927] |
Thu, 24 May 2018 13:14 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
laredoeneko wrote on Thu, 24 May 2018 14:06thank you very much for you fast replay.
there is no problem if the pl takes more time than a complete update, i need have some records update as soon as possible, not the entire table.
If you need some right now, then why not just run the update for those rows, with appropriate WHERE clause?
Quote:
is EXECUTION_DATE column indexed?
yes, it is a pk with another row, and also there is a index in this column
I am assuming you mean another column, not row.
If you use a loop to update row by row and commit after every X rows, you may encounter ORA-01555: snapshot too old.
|
|
|
|
|
|
Re: update huge table every n rows [message #669999 is a reply to message #669960] |
Tue, 29 May 2018 04:20 |
cookiemonster
Messages: 13917 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
BEGIN
LOOP
update test_update
set execution_date=execution_date + interval '2' day
where execution_date > CURRENT_TIMESTAMP - interval '4' day
and rownum < 10000;
commit;
EXIT WHEN SQL%ROWCOUNT = 0;
END LOOP;
END;
|
|
|
Re: update huge table every n rows [message #670013 is a reply to message #669960] |
Tue, 29 May 2018 09:56 |
|
JPBoileau
Messages: 88 Registered: September 2017
|
Member |
|
|
Depending on how much time you can spend with the table being locked, you can decide from one of the following:
1) Do an UPDATE ... WHERE ROWNUM <= 10000 ... COMMIT [Will take longer overall]
2) Do a PARALLEL UPDATE: UPDATE /*+ PARALLEL(TABLE 8 ) */ TABLE ... [Will take much less time, but will be much more intensive on the DB and also depends on the number of available CPU's on your server]
set timing on
alter system flush shared_pool;
alter system flush buffer_cache;
DEV1> declare
2 n number := 0;
3 begin
4 loop
5 update mytb1 set last_update_ts = trunc(sysdate+1)
6 where last_update_ts < sysdate and rownum <= 10000;
7 exit when SQL%ROWCOUNT = 0;
8 n := n + SQL%ROWCOUNT ;
9 commit;
10 end loop;
11 dbms_output.put_line(to_char(n) || ' rows processed');
12 end;
13 /
10000000 rows updated.
PL/SQL procedure successfully completed.
Elapsed: 00:03:47.80
set timing on
alter system flush shared_pool;
alter system flush buffer_cache;
DEV1> update /*+ PARALLEL(mytb1, 8) */ mytb1 set last_update_ts = trunc(sysdate+1)
where last_update_ts > sysdate;
10000000 rows updated.
Elapsed: 00:00:50.59
JP
[Updated on: Wed, 30 May 2018 07:40] Report message to a moderator
|
|
|