Archives of the TeradataForum

Message Posted: Mon, 25 Nov 2002 @ 18:40:49 GMT

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

The Perl script below will read a file of DDL (usually the output of SHOW TABLE) and compute the row length for fixed-length rows only. VARCHAR, BYTE, and VARBYTE are not supported but could easily be added.

Caution! If your table contains compressed columns, the rows will be different lengths; this script will compute the maximum. The actual average would have to be determined empirically.

#!/usr/bin/perl -w
# Compute row lengths for fixed-length columns only.
# Stdin: CREATE TABLE statements.

%length = ('INTEGER' => 4, 'SMALLINT' => 2, 'BYTEINT' => 1,
  'CHAR' => -1, 'DECIMAL' => -1, 'DEC' => -1,
  'DATE' => 4, 'TIME' => 6, 'TIMESTAMP' => 10,
  'REAL' => 8, 'FLOAT' => 8,
);

$rl = 0;            # Row length
$comp = 0; $nulb = 0;  # Number of compressed & nullable columns
while (<>) {
   chomp;  tr/a-z/A-Z/;
   if (m/CREATE( SET| MULTISET)? TABLE ([\w\.]+)/i) {
      print "$2\n";  # Table name
   }
   if (/\b(INTEGER|SMALLINT|BYTE|CHAR|DEC|TIME|DATE|REAL|FLOAT)/i ) {
      $comp++ if /COMPRESS/;
      $nulb++ unless /NOT NULL/;
      m/\w+\s+([\w\(\d]+)/;
      ($type, $len) = split /\(/, $1;
      $pl = $length{$type};   # Preliminary length
      if ($pl > 0) {
         $rl += $pl;
      } else {
         $rl += true_length($type, $len);
      }
   }
   if (/PRIMARY INDEX/) {
#--- Account for compress and null bits
      $rl += int(($comp + $nulb) / 8);
      print "  Row length: $rl     with overhead: ", $rl+14, "\n";
      $rl = 0; $comp = 0; $nulb = 0;
   }
}

#--- Compute the true length in bytes of a CHAR or DEC.
sub true_length {
 my ($type, $len) = @_;
 if ($type =~ /DEC/) {
    return ($len >= 10) ? 8 : ($len >= 5 ) ? 4 :
           ($len >= 3 ) ? 2 : 1;
 } elsif ($type =~ /CHAR/) {
    return $len;
 } else {
    return 0;
 }
}

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