update large table
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: update large table

  1. #1
    Join Date
    Feb 2007
    Posts
    212

    update large table

    Hi friends,

    I have a big table like 40 million rows....I want to update it as fast as I could
    without using redologs (logging) or rollback or temp etc. Is this possible?

    Does this command help?

    update table EMP nologging parallel 8 set fullname=upper(trim(fullname));


    thanks a lot

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    no you cannot do that hope you reliase how dangerous it is too even if you could

  3. #3
    Join Date
    Feb 2007
    Posts
    212
    pls tell me dear why....

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    I suggest you to read oracle concepts manual

  5. #5
    Join Date
    Apr 2006
    Posts
    9
    hi,
    tri writing a procedure to do that.it realy decrease the time used to do an update on such a large table.
    It realy works for me on a table with over 32 mil rows.
    However , it will take a lot of time(and resource) if you use the 'clasy' way !

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    why is a procedure quicker than a simple sql statement

  7. #7
    Without logging? Sure -- disable all constraints against the table, do a CREATE TABLE AS SELECT that creates the modified table, drop the existing table, rename the new table to the old table, then re-enable constraints.

  8. #8
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    redo - yes
    temp - I dont think its going to use temp because no sorting will happen
    rbs - I'm afraid no.

    Trim wouldn't really help unless your datatype is char or your data is
    from other source.

    but this certainly will help you, I call it the REYDP_HUGEUPDATE.

    DECLARE
    t_block_no VARCHAR2(18);

    cursor blockcols is
    select distinct substr(ROWID,10,6)
    from emp;

    begin
    open blockcols;

    LOOP
    fetch blockcols into t_block_no;
    exit when blockcols%NOTFOUND;

    UPDATE EMP SET fullname=UPPER(TRIM(fullname))
    WHERE SUBSTR(ROWID,10,6) = t_block_no;
    commit;
    END LOOP;
    close blockcols;
    END;
    /

    ---good luck--------
    ---------------

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Ray,

    PL/SQL Block always runs slower than a single update.

    For a simple update, do it in a single statement. If possible, use parallel DML.

  10. #10
    Join Date
    Mar 2006
    Location
    Buenos Aires, Argentina
    Posts
    25
    Quote Originally Posted by tamilselvan
    Ray,

    PL/SQL Block always runs slower than a single update.

    For a simple update, do it in a single statement. If possible, use parallel DML.
    tamilselvan :

    can you be more specific about parallel DML? what exaclty do you have in mind?

    regards,

    daniel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width