DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Heavy Update

  1. #1
    Join Date
    Feb 2005
    Location
    India
    Posts
    3

    Heavy Update

    Hi,

    I have a table which contains 46 million rows. I have added a new field in that table. Now i want that field should contain say 'Y' for every row. For that, if i go for update table set new_field='Y' it takes around 48 hrs. Please tell me how can i reduce the time taken by the query or suggest some other way for doing this.

    Thanks N regards
    Win

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Apr 2003
    Posts
    353
    From your post there
    /*************
    You're not going to be much quicker like that. If you really want speed then you'll have to create a new table in nologging mode by selecting the current table, then truncate the old table, disable the indexes, add the new column, set it to nologging, insert /*+ append */ back into it (and include the 'Y' column), then rebuild indexes in nologging mode and analyze the table again.
    ******************/

    The following steps are followed there.
    1 Create a table with the by selecting from current table.
    2 Truncate the old table
    3 insert back to original table ( Takes more than half an hour??).
    (after dropping indexes)
    4 Rebuild indexes

    How about

    1. create a table with by selecting the fields and also the Y column replaced
    2. drop the old table
    3. rename the new table to the old table
    4. create indexes

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Could do, yes, but then you have to fiddle around with recreating any triggers, grants, indexes, recompiling views/procedures/MV's etc -- if you didn't have any of those then your approach would be a better one. Mine was a blend between performance and laziness, I guess!
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Apr 2003
    Posts
    353
    Yes. Export the table without rows=n.
    After rename the table
    rebuild the indexes.
    then import with the above export with ignore=y option.
    Then compile the dependencies, if timestamp needs to be applied, do the same.

  6. #6
    Join Date
    Apr 2003
    Posts
    353
    Pls read the first line as

    Yes. Export the table with rows=n.

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ==
    Hi,

    I have a table which contains 46 million rows. I have added a new field in that table. Now i want that field should contain say 'Y' for every row. For that, if i go for update table set new_field='Y' it takes around 48 hrs. Please tell me how can i reduce the time taken by the query or suggest some other way for doing this.

    Thanks N regards
    Win

    ===
    Hi Win,

    What is your oracle version?
    What is the PCTFREE value set for the table?
    What is the average row length?
    How many datafiles are allocated to the tablespace in which the table sits?
    How many CPUs does the box have?
    What is your SGA Size?

    If you want to win, you must supply more above.

    No exp/imp, no create table, nothing...

    The simple solution I am thinking of is "parallel update".

    PHP Code:
    Example:
    set time on timing on 
    spool upd_my_table_2
    .log 

    drop index scott
    .my_table_m3 

    --- 
    1 option 
    alter table scott
    .my_table parallel 8
    alter index scott.my_table_p1 parallel 8
    alter table scott.tamil_temp_con_addr parallel 8
    alter index scott.tamil_temp_con_addr_idx parallel 8

    commit
    alter session enable parallel dml 
    alter session enable parallel query 

    update /*+ parallel(a,8) */ scott.my_table a 
       set a
    .pr_addr_id = ( select addr_per_id 
                              from scott
    .tamil_temp_con_addr c 
                             where c
    .accnt_id a.row_id
    where exists select null 
                    from scott
    .tamil_temp_con_addr c 
                    where c
    .accnt_id a.row_id) ; 
    commit
    alter session disable parallel dml 
    alter session disable parallel query 
    alter table scott.my_table    noparallel 
    alter index scott.my_table_p1 noparallel 

    spool off 
    Tamil
    Last edited by tamilselvan; 03-03-2005 at 08:17 PM.

  8. #8
    Join Date
    Feb 2005
    Location
    India
    Posts
    3
    I don't want Rowid to be changed. In this case Rowid will be different.

    Thanks
    Win
    Vijay

  9. #9
    Join Date
    Apr 2003
    Posts
    353
    very useful query from Tamil. But need to be tested for time (Though parallel.) and UNDO.

    Where is the change of rowid occuring? Rowid will change in the
    options given by Slimdave and me, not in Tamil's one

  10. #10
    Join Date
    Feb 2005
    Location
    India
    Posts
    3
    Following is my test table on which i'm doin the testing. Once it is done i'll test it on testing environment. Till now i'm testing it on my local Database.

    TABLE NAME is A1 and INDEX NAME is Index_1, Which is created on Case_id and COL1

    Name Null? Type
    ----------------------------------------- -------- ------------
    CASE_ID NUMBER(15)
    COL1 NOT NULL VARCHAR2(20)
    COL2 NOT NULL VARCHAR2(20)
    COL3 NOT NULL NUMBER(20)
    NCOL VARCHAR2(50)

    Now NCOL is newly added field n want to update it by 'Y'. This test table is having around 20 million rows.


    GIRI n TAMIL, can u plz elaborate the solution provided by TAMIL.

    Thanks n Regards
    Win
    Vijay

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