Update table taking too long.

[edwds]

I am trying to Update an Oracle 9i table, which has 275,000 records. The Source and Target tables are SAME.

When I load the data to a file, it takes less than a minute where as UPDATING the same data to a table is taking too long.....
When I monitor the job in Director it hangs at the Target table stage saying: Dynamic_RDBMS_32,0: Logging delayed metadata.

I haven't changed any properties on the target DRS stage.

Update Action is: Update existing rows only
The Transaction Isolation is default: Read Committed.
Array size is 1
Transaction size is 0
Partitioning is AUTO.

Any suggestions to make it fast.

Thanks in advance.

[DSguru2B]

Increase the array size to about 250. See how does that effect the performance.

[ray.wurlod]

The job needs to wait until the 275,000 row transaction is assembled and successfully committed. This is your choice, since your job specifies "all rows are one transaction" (transaction size 0).

You also need to check what obstacles exist to update operations (which are slow in any case). What indexes and constraints exist on the table?

[keshav0307]

i think its more related to your sql query tuning.
Do you have proper Indexes on the target table... and on the columns in the where cluase of the update query...

[Rajesh_kr82]

I think you are not using the proper keys and indexes to update the table. So you can try using the correct keys and then increase your transaction size. That should do.

[ray.wurlod]

The transaction size is already "all rows"!

You can demonstrate that the problem is not in DataStage by changing the job to write its records to a file. This job will fly. Then create a second job that does nothing but read that file and update the rows.

[evans036]

if the ratio of rows being updated (verses rows in the table) is high (eg > 20%), try dropping all indexes not required for the update. you may also see perf boost if you sort the data in the order of the remaining index.

then recreate indexes.

good luck,

steve

[avi21st]

Use a user defined update statement...how many CPU you have at your database side?

Mainly in Oracle you can use parallel DML to fast up the process...

something like-

Code: Select all

update /*+ parallel(c,4) */
(select /*+ parallel(a,4) parallel(b,4) */ 
a.column1, 
b.column1
from schema.table1a , 
schema.table b
on a.id1 = b.id2) c     
set old_val = new_val 

In Oracle USE_HASH and PARALLEL are the keywords for tuning the query. Also you have a large volume of data you should partition the target table accordingly.

It is Oracle level issue for sure....so let me know if you need help

[ray.wurlod]

Doesn't the Oracle Enterprise stage do this automatically? It ought to. IMHO, of course.