Update Records

[Former Member]

Hi All,

Based on Two table join condition, the result records need to be update in Target Table.

Scenario:

Source: Table1 and Table2

Target: Table1

Source Table1 count: Around 20 Crores

Soruce Table2 Count: Around 100 Records

Join Condition: Three Columns, One Column will not have Index in Table1

Soure Tables Table1 and Table2 are in Different database.No DB Link

Currently the Query is taking around 20 to 30 minutes. Plan to increase the performance.

Query transfory Query:

select * from table1, Table2

where (((Table1.column1 = table2.column1)

AND (table1.column2 = table2.column2))

AND ( ((table1.column3 = table2.column3)

  OR (table1.column3 != table2.column3)) ))

Thanks

Suresh

[former_member187605]

I would first copy table1 into table2 database. Then do the join and update.

You could consider building an incremental copy if this is a repeating job to speed up the process even more.

[Former Member]

Have limitation to copy Table1 to Table2 database. Hence I have to improve the performance without copying?

Is there any way?

[former_member187605]

Sorry, my mistake. I meant copy table2 to table1 database. If that's not possible either, you can try the following.

Make Table1 the driving table in the join by giving it a higher Join rank than Table2 (make sure they're both non-zero). Do not cache Table1, increase its Array fetch size; cache Table2 (that's the default setting). Set the data flow Cache Type property to In-Memory.

I hope that will help. But it looks a bit like willing to send you on a mission to the moon, but they don't want to give you a rocket .