Archives of the TeradataForum

Message Posted: Wed, 06 Feb 2002 @ 16:49:46 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Matt:

You are absolutely correct about the impact of the transient journal (TJ) on any SQL maintenance operation.

Then, you compound it with a join. This issue adds to the time due to the preparation performed by Teradata to do the join (temp row redistribution in SPOOL).

So, here are a couple of things that you might experiment with. First, makesure you have done a recent COLLECT STATISTICS on the join columns from all joined tables. This might make the join efficient enough to finish in a reasonable time.

Second, I have written about "Fast Path Processing." This technique allows you to do an UPDATE without performing an UPDATE. Let me explain. This technique avoids the TJ because you are really doing an INSERT/SELECT into an empty table. The SELECT performs the necessary "update" as a process of selecting the updated info in the join.

To use your example:

INS INTO New_table1
   SEL Table2.Col4   /* instead of Col1 = */
       ,Table2.Col5  /* instead of Col2 = */
    /* of course your other columns go here */
   FROM Table1
   WHERE Col3 = 'Apples'
      AND Table1.Col6 = Table2.Col6;

This should be extremely fast. However, it requires enough free PERM space, the DROP of table1 and the RENAME of New_table1.

A third approach might be to EXPORT the updated data using a SELECT in BTEQ. Then, do the UPDATE using MULTI-LOAD instead of SQL. This too eliminates the TJ. The problem here is that for a time, the table will be locked. So, it is best to do it at night.

I hope that one or both of these provide some relief for your situation.

Best regards,

Mike

	<Prev	Next>		<<First	<Prev	Next>	Last>>