update large table - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 26

Thread: update large table

  1. #11
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by dgcampos
    tamilselvan :

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

    regards,

    daniel
    Please read my paper.

    http://oracleact.com/papers/oracle_parallel_dml.html

  2. #12
    Join Date
    Sep 2005
    Posts
    278
    Dear Tamil, While trying example provided in the link given by you I got the following problem

    Code:
    SQL> SELECT count(*) FROM myobjects;
    
      COUNT(*)
    ----------
      10427392
    
    Elapsed: 00:00:13.06
    I'm trying to run Parallel DML on that table, I have following index on that table
    Code:
    SQL> SELECT index_name, column_name FROM user_ind_columns WHERE table_name = 'MYOBJECTS';
    
    INDEX_NAME					 COLUMN_NAME
    ------------------------------ ------------------------------
    INDX_MYOB					  OBJECT_NAME
    
    Elapsed: 00:00:00.01
    Here is the code for Parallel DML, I'm running only one session which is current. I dont know how comes I got an error saying "DEADLOCK DETECTED"

    Code:
    SQL> alter session enable parallel dml;
    
    Session altered.
    
    SQL>
    SQL> alter session set db_file_multiblock_read_count=128;
    
    Session altered.
    
    SQL> set timin on
    SQL>
    SQL>
    SQL> UPDATE /*+ full(myobjects) parallel(myobjects, 8)*/ myobjects
      2  SET object_name = upper(object_name);
    update /*+ full(myobjects) parallel(myobjects, 8)*/
    *
    ERROR at line 1:
    ORA-12801: error signaled in parallel query server P015
    ORA-00060: deadlock detected while waiting for resource

  3. #13
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    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.
    Yes Tamil, I do agree with you.
    In fact I myself have posted before,
    long time ago about using PARALLEL DML
    also here in this forum.


    PL/SQL Block always runs slower than a single update.-
    - yes, it was actually a sort of a test to the poster. Davey, actually posted contesting the use of pl/sql to update before my post.

    It'a trick that will work for small table
    but not in huge one...heheh..my bad.
    ---------------

  4. #14
    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

  5. #15
    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

  6. #16
    Join Date
    Sep 2005
    Posts
    278
    Thanks Tamil,

    Its working fine now. Thanks a lot

  7. #17
    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

  8. #18
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    tamil isnt doing it

  9. #19
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    There are some occasions you need to update a big table.
    For example, a ETL process may need to clean up the source data in a staging table in a DW system before loading into FACT table.

    It's nothing wrong using parallel DML as long as you achieve the goal in a shorter time.

    If you are not using the feature, then what's the purpose of it?

  10. #20
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    1) When we talk about danger we mean update with no redo generation
    2) There is no danger since that's not possible
    3) If you do not generate redo and the database fail and you bring the file from backup, you'll loose your table

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