Columnar NoPI table- calculate deleted rows partition perm space

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Columnar NoPI table- calculate deleted rows partition perm space

How can we calculate space occupied by deleted rows partition of a columnar table. There are no fastpath deletes.

Tags (1)
1 REPLY
Teradata Employee

Re: Columnar NoPI table- calculate deleted rows partition perm space

One way to find out the information about deleted rows is collecting summary stats on the CP table.  (DelRowCount * AvgRowSize) shall give pretty good information about the perm space of the deleted rows.  "COLLECT SUMMARY STATS"  doesn't read the rows, only the table header. So it's fast. 

 

collect summary stats on t100k_cp_b;

*** Update completed. One row changed.
*** Total elapsed time was 1 second.


BTEQ -- Enter your SQL request or BTEQ command:

 

show summary stats values on t100k_cp_b;

*** Text of DDL statement returned.
*** Total elapsed time was 1 second.

---------------------------------------------------------------------------
COLLECT SUMMARY STATISTICS
ON ILE_DB1.t100k_cp_b
VALUES
(
/** TableLevelSummary **/
/* Version */ 6,
/* NumOfRecords */ 37,
/* Reserved */ 0.000000,
/* Reserved */ 0.000000,
/* CurrSysInsertCnt */ -1,
/* CurrSysDeleteCnt */ -1,
/* CurrSysInsDelResetTS */ TIMESTAMP '9999-12-31 23:59:59-00:00',
/* NumOfCurrSysUpdateCols*/ 0,
/* SummaryRecord[1] */
/* Temperature */ 0,
/* TimeStamp */ TIMESTAMP '2016-10-07 04:12:22-00:00',
/* NumOfAMPs */ 4,
/* OneAMPSampleEst */ 3828,
/* AllAMPSampleEst */ 3827,
/* RowCount */ 100000,
/* DelRowCount */ 0,
/* PhyRowCount */ 3827,
/* AvgRowsPerBlock */ 65.391238,
/* AvgBlockSize (bytes) */ 99635.000000,
/* BLCPctCompressed */ 0.00,
/* BLCBlkUcpuCost (ms) */ 0.000000,
/* BLCCompRatio */ 0.000000,
/* AvgRowSize */ 426.000000,
/* Reserved */ 0.000000,
/* Reserved */ 0.000000,
/* Reserved */ 0.000000,
/* StatsSkipCount */ 0,
/* SysInsertCnt */ 0,
/* SysDeleteCnt */ 0,
/* SysUpdateCnt */ 0,
/* SysInsDelLastResetTS */ TIMESTAMP '9999-12-31 23:59:59-00:00'

}