Home Page for the TeradataForum
 

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023