Archives of the TeradataForum

Message Posted: Mon, 28 Feb 2011 @ 16:13:23 GMT

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

Hello,

Any idea what takes so long in the END TRANSACTION step when I delete from a partitioned table?

I deleted 2.500.000.000 lines (518 partitions) in one step (3 in EXPLAIN), it took 20 seconds. Then, the END TRANSACTION step took 1 hour 15 minutes.

The explain is below. I've seen this behavior few times when I deleted a significant number of rows based on partition but never really understood the cause. Is it the number of rows, the partitioning or something else? If someone can educate me on this, I would appreciate.

The Edt step (5 in EXPLAIN) exploded in terms of ElapsedTime, CPUTime and IOCount.

     1) First, we lock a distinct dbname."pseudo table" for write on a
        RowHash to prevent global deadlock for dbname.tbname.
     2) Next, we lock dbname.tbname for write.
     3) We do an all-AMPs DELETE of 518 partitions of
        dbname.tbname with a condition of (
        "(dbname.tbname.col_partition >= DATE '2008-08-01') AND
        (dbname.tbname.col_partition <= DATE '2009-12-31')").
     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.

I am on V2R6 with 62 AMPs.

Thanks in advance.

Tomas Gorner

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