Forum Stats

  • 3,770,153 Users
  • 2,253,079 Discussions
  • 7,875,349 Comments

Discussions

Update 340 Million of rows.

Hi,

I needs to update huge partitioned data ( more than 340 millions) and following was the existing query which were using it so far, but it wasn't finishing it now due to huge change and getting SPACE issues as well.

We tried multiple ways like creating temp table for the sub-query and creating indexes on group clauses..etc, and even either(INDEX/full scan) of these scans also helping to finish.

So please suggest me to fix this issue and does any of the following help us.. ?

  1. BULK - forall
  2. CTAS

Query:

UPDATE w_xxxx

  SET effective_end_date = to_date('07-AUG-21 09:31:02','DD-MON-YY HH24:MI:SS') - 1/86400, current_flag = 0

  WHERE effective_start_date <> to_date('07-AUG-21 09:31:02','DD-MON-YY HH24:MI:SS') AND current_flag = 1 AND datasource_id = 1 AND project_object_id IN (

   SELECT DISTINCT project_object_id FROM (

    SELECT count(*) cnt, project_object_id, resource_object_id, day_wid

    FROM w_xxxx

    WHERE datasource_id = 1

    AND current_flag = 0

    GROUP BY project_object_id, resource_object_id, day_wid)

   WHERE cnt > 1);

Tagged:

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 690 Silver Trophy

    getting SPACE issues

    UNDO?

    Update by chunks and commit each one. The chunks can be made with rowid between built from dba_extents.

    Ensure that the data remains unchanged by other transactions.


  • Thanks for respond. Yes we are getting UNDO/TEMP issues and is never finishing even after days.

    can you give me an small example for the given scenario . ?

  • BluShadow
    BluShadow Member, Moderator Posts: 41,493 Red Diamond

    For starters you can simplify your sub-query by using the HAVING clause and only select the distinct column you require from the results of that...

    e.g.

    UPDATE w_xxxx
      SET effective_end_date = to_date('07-AUG-21 09:31:02','DD-MON-YY HH24:MI:SS') - 1/86400
         ,current_flag = 0
      WHERE effective_start_date <> to_date('07-AUG-21 09:31:02','DD-MON-YY HH24:MI:SS')
      AND   current_flag = 1
      AND   datasource_id = 1
      AND   project_object_id IN (
              SELECT distinct project_object_id -- select distinct of the required column here
              FROM   w_xxxx
              WHERE  datasource_id = 1
              AND    current_flag = 0
              GROUP BY project_object_id, resource_object_id, day_wid
              HAVING count(*) > 1 -- Filter here rather than in outer subquery
              );
    
    
    


  • Thank you. Actually I tried that, was not seeing any improvement.

  • User_H3J7U
    User_H3J7U Member Posts: 690 Silver Trophy

    we are getting UNDO/TEMP issues and is never finishing even after days

    If the request does not complete, the problem with the space can only be when resumable_timeout is set.

    In any case, you can't see an issue with both temp and undo at the same time.

  • cormaco
    cormaco Member Posts: 1,723 Bronze Crown

    You could try DBMS_PARALLEL_EXECUTE:

    This package lets you incrementally update table data in parallel, in two high-level steps.

    1. Group sets of rows in the table into smaller-sized chunks.
    2. Run a user-specified statement on these chunks in parallel, and commit when finished processing each chunk.


    ApparaoAkki-Oracle
  • GregV
    GregV Member Posts: 3,069 Gold Crown

    Hi,

    Do these rows account for most of the table's? If yes, you may consider using CTAS. I had to do a similar task recently and using CTAS for updating a whole table with 150 millions rows took about 20 min (including the index re-creation).

    ApparaoAkki-Oracle
  • Yeah, I could try this and seems good one. Will try .

    Thanks.

  • wtolentino
    wtolentino Member Posts: 18 Blue Ribbon
    edited Aug 13, 2021 7:33PM

    if you can get the plan and post it in this thread the better

    explain plan for
    UPDATE w_xxxx
      SET effective_end_date = to_date('07-AUG-21 09:31:02','DD-MON-YY HH24:MI:SS') - 1/86400, current_flag = 0
      WHERE effective_start_date <> to_date('07-AUG-21 09:31:02','DD-MON-YY HH24:MI:SS') AND current_flag = 1 AND datasource_id = 1 AND project_object_id IN (
       SELECT DISTINCT project_object_id FROM (
        SELECT count(*) cnt, project_object_id, resource_object_id, day_wid
        FROM w_xxxx
        WHERE datasource_id = 1
        AND current_flag = 0
        GROUP BY project_object_id, resource_object_id, day_wid)
       WHERE cnt > 1);
    
    select * from table(dbms_xplan.display);
    


    you can aslo try to collect stats
    execute dbms_stats.gather_table_stats(ownname => [schema], tabname => [table]);
    
    note: stats collection for table with millions of rows will take longer to complete
    

    or if you have partitioned table -- this is untested

    UPDATE table_name partition (partition_Name)
    ...
    
    

    or isolate the problem by trying which one of these sub-query is better. if the without the distinct is better try it on your update statement.

    -- without the distinct
    select iv.project_object_id
      from (select count(*) over (partition by project_object_id, resource_object_id, day_wid order by project_object_id, resource_object_id, day_wid) cnt, 
                   row_number() over (partition by project_object_id, resource_object_id, day_wid order by project_object_id, resource_object_id, day_wid desc) rnum,
                   project_object_id
             from w_xxx
            where datasource_id = 1
              and current_flag  = 0) iv
     where iv.cnt  > 1
       and iv.rnum = 1;
    
    -- with distinct
       SELECT DISTINCT project_object_id 
         FROM (SELECT count(*) cnt, project_object_id, resource_object_id, day_wid
                 FROM w_xxxx
                WHERE datasource_id = 1
                  AND current_flag = 0
               GROUP BY project_object_id, resource_object_id, day_wid)
        WHERE cnt > 1;
    


  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,790 Gold Crown

    Which version of Oracle

    How is the table partitioned

    is "w_xxxx" the same table in the main query and subquery

    How many rows do you get from the "select distinct project_id".

    Is there any possibility that all the rows for a given project_id would be restricted to a single partition, and if so could you therefore update one partition at a time.

    Regards

    Jonathan Lewis