Archives of the TeradataForum

Message Posted: Mon, 07 Jul 2003 @ 21:07:20 GMT

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

When you send a multi-statement request to the parser (which is what you're asking bteq to do by not terminating a line with a semicolon until the last line), it gets to see all the statements at once. Sometimes, this will allow the optimizer to build a more efficient plan.

An example:

ins
;ins
;ins ;

where target is initially empty.

When inserting into an empty table, only one row has to be sent to the transient journal. When inserting into a populated table, every row inserted has to be journaled.

If the optimizer processed each statement separately, all the rows in the second and third inserts would be journaled. But when the optimizer sees all three statements, it will put the results of all three statements into a spool file, then merge the spool into the target table. Since there's only one insert, only one row will go to the transient journal. Also all the inserts will be appends which is more efficient for the file system.

You can verify plans like this be checking the explain.

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