Home » SQL & PL/SQL » SQL & PL/SQL » update huge table every n rows (oracle 12c )
update huge table every n rows [message #669922] Thu, 24 May 2018 10:02 Go to next message
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 #669923 is a reply to message #669922] Thu, 24 May 2018 10:13 Go to previous messageGo to next message
BlackSwan
Messages: 26649
Registered: January 2009
Location: SoCal
Senior Member
>is it possible with a pl/sql update the table every 1000 rows?
Yes, it is possible, but total elapsed time will only INCREASE by doing so.

EXECUTION_DATE column is type TIMESTAMP so why does DELETE compare it to DATE datatype & force implicit datatype conversion?

post EXECUTION PLAN for existing DELETE statement

is EXECUTION_DATE column indexed?
Re: update huge table every n rows [message #669925 is a reply to message #669922] Thu, 24 May 2018 10:30 Go to previous messageGo to next message
cookiemonster
Messages: 13789
Registered: September 2008
Location: Rainy Manchester
Senior Member
How many rows will be updated?
Re: update huge table every n rows [message #669926 is a reply to message #669925] Thu, 24 May 2018 13:05 Go to previous messageGo to next message
laredoeneko
Messages: 21
Registered: November 2017
Junior Member
all around 50M
Re: update huge table every n rows [message #669927 is a reply to message #669923] Thu, 24 May 2018 13:06 Go to previous messageGo to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
laredoeneko wrote on Thu, 24 May 2018 14:06
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.
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 #669929 is a reply to message #669928] Thu, 24 May 2018 14:03 Go to previous messageGo to next message
laredoeneko
Messages: 21
Registered: November 2017
Junior Member
you are right i can update only some rows, in order to get some values update as fast as possible.


sorry for my mistake, you are right again, another column has a pk and there is specific index for the in column execution_date

thank you for you advice and for your time
best regard
Re: update huge table every n rows [message #669953 is a reply to message #669929] Fri, 25 May 2018 09:11 Go to previous messageGo to next message
Bill B
Messages: 1927
Registered: December 2004
Senior Member
update test_update
set execution_date=execution_date + interval '2' day
where execution_date > CURRENT_TIMESTAMP - interval '4' day;
Re: update huge table every n rows [message #669960 is a reply to message #669953] Fri, 25 May 2018 12:58 Go to previous messageGo to next message
laredoeneko
Messages: 21
Registered: November 2017
Junior Member
thank you very much, this is the query but it takes more than 5 hour to update the entire table.
i ask for a procedure to update and commit every few rows in other to get some value data as soon as possible. i undertand that this procedure takes more than you query.
anyway, thank you
Re: update huge table every n rows [message #669999 is a reply to message #669960] Tue, 29 May 2018 04:20 Go to previous messageGo to next message
cookiemonster
Messages: 13789
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 Go to previous message
JPBoileau
Messages: 85
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

Previous Topic: relational() function
Next Topic: wants to generate XML
Goto Forum:
  


Current Time: Wed Jan 29 01:23:09 CST 2020