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

Hybrid View

  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,333
    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,447
    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
    Nov 2006
    Location
    Sofia
    Posts
    630
    So, to summarize the thread,
    Quote Originally Posted by davey23uk
    no you cannot do that hope you reliase how dangerous it is too even if you could

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    To TABREAZ,

    You need to increase the INITRANS value for the index.

    See below:

    create index t1_idx on t1(object_name)
    pctfree 30 initrans 9
    tablespace large_01_idx nologging parallel 8
    /
    11:10:32 SQL> alter session enable parallel dml;

    Session altered.

    Elapsed: 00:00:00.00
    11:10:40 SQL> alter session force parallel dml parallel 8 ;

    Session altered.

    Elapsed: 00:00:00.00
    11:11:25 SQL> update /*+ full(t1) parallel(t1,8) */ t1
    11:11:34 2 set object_name = lower(object_name);

    1855968 rows updated.

    Elapsed: 00:04:54.27

  8. #8
    Join Date
    Sep 2005
    Posts
    278
    Thanks Tamil,

    Its working fine now. Thanks a lot

  9. #9
    Join Date
    Feb 2007
    Posts
    212
    Quote Originally Posted by Bore
    So, to summarize the thread,
    So whats the danger plsssssssssssssss

    Why is TAMIL doing it

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

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