Archives of the TeradataForum

Message Posted: Wed, 22 Jun 2011 @ 14:17:44 GMT


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

Subj:		Different EXPLAIN on identical systems

From:		Gorner, Tomas

Hi everyone,

I have a real head-scratcher here:

I have 2 identical, 1-node Teradata systems:

RELEASE V2R.06.01.01.84
VERSION 06.01.01.84

and 2 identical tables, one on each system.

However, when I Explain a simple DELETE statement on system 1, it adds an extra line to the explain plan compared to system 2. The line is: "We spoil the parser's dictionary cache for the table."

Now, I know that flushing parser's cache can significantly slow-down my deletes. Especially when there are thousands of them, line-per-line. I am aware of the fact that a delete in batch mode would be much more efficient here. However, I am still curious about a reason for this behaviour.

If someone can enlighten me, I'd be grateful.

     DEL FROM DB.TableName WHERE COLUMN2 = 2

Explain System 1:

`Explanation --------------------------------------------------`
	`1)`	`First, we lock a distinct DB."pseudo table" for write on a RowHash to prevent global deadlock for DB.TableName.`
	`2)`	`Next, we lock DB.TableName for write.`
	`3)`	`We do an all-AMPs DELETE from DB.TableName by way of an all-rows scan with a condition of ("DB.TableName.COLUMN2 = 2.").`
	`4)`	`We spoil the parser's dictionary cache for the table.`
	`5)`	`Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.`
	`->`	`No rows are returned to the user as the result of statement 1.`

Explain System 2:

`Explanation --------------------------------------------------`
	`1)`	`First, we lock a distinct DB."pseudo table" for write on a RowHash to prevent global deadlock for DB.TableName.`
	`2)`	`Next, we lock DB.TableName for write.`
	`3)`	`We do an all-AMPs DELETE from DB.TableName by way of an all-rows scan with a condition of ("DB.TableName.COLUMN2 = 2.").`
	`4)`	`Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.`
	`->`	`No rows are returned to the user as the result of statement 1.`

The DDL is as follows:

     CREATE SET TABLE DB.TABLENAME ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           COLUMN1 INTEGER NOT NULL,
           COLUMN2 DECIMAL(8,0),
           COLUMN3 BYTEINT,
           COLUMN4 VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,
           COLUMN5 SMALLINT,
           COLUMN6 SMALLINT,
           COLUMN7 TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0)) PRIMARY INDEX ( COLUMN1, COLUMN2 );

Thanks a lot.

Tomas


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

Attachments

Library

Quick Reference