RE: UPDATE vs LEFT OUTER JOIN

jklee — Thu, 30 Nov 2006 16:12:51 GMT

The left-outrr join approach should be faster since you will avoid the transient logging that occurs for the update in step 1 of the original approach.

Make the PI of both table A and C the same as the joined columns (col1/cola, col2/colb) to keep the join amp local.

Also, it db2.tableA has the same PI, the insert into that table will laso be amp local.

UPDATE vs LEFT OUTER JOIN

Ajay Jr — Tue, 14 Nov 2006 15:51:34 GMT

Hi All,

Wants your ideas on update vs Left Outer Join wrt Performance

I have 2 tables Table A and Table C in two databases.

Database 1:- Table_A (daily Full refresh)

Database 2: Table_A (Incremental Loading) and Table_C

So total 3 tables

table_A having col 1 , col 2 and col 3
table_C having col a , col b and col c

Currentlty I am doing

Step 1)

Update database1.table_A A, database2. table_C C
set A.col3 = C.col c
where A.col1 = C.col a and A.col2 = C.col b

after this

Step 2)

insert into database2.table_A
as select * from database1.table_A

But updates are taking really very longtime.. I am getting almost 10 Million records every day for database1.table_A and also very huge number records in database2.table_C as well (approx 40 million) . So step 1 taking much time.

So I am thinking of using following SQL to combine step 1 and stpe 2 and removing update

insert into database2.table_A
(
col 1,
col 2,
col 3
)
select
(A.col 1,
A.col 2,
C.col c
)
from database1.table_A A
LEFT OUTER JOIN database2.table_C C
ON A.col1 = C.col a and A.col2 = C.col b;

Is this OK? or any other ideas to improve performance?

Teradata Forums / Teradata / Teradata Database / UPDATE vs LEFT OUTER JOIN / Latest Posts

RE: UPDATE vs LEFT OUTER JOIN

UPDATE vs LEFT OUTER JOIN