update large table

[tamilselvan]

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

[tabreaz]

Dear Tamil, While trying example provided in the link given by you I got the following problem

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

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"

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

[reydp]

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.

[Bore]

So, to summarize the thread,

no you cannot do that hope you reliase how dangerous it is too even if you could

[tamilselvan]

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

[tabreaz]

Thanks Tamil,

Its working fine now. Thanks a lot

[jennifer2007]

So, to summarize the thread,

So whats the danger plsssssssssssssss

Why is TAMIL doing it

[davey23uk]

tamil isnt doing it

[tamilselvan]

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?

[Bore]

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