Copying giant table is very slow

Database
Highlighted

Re: Copying giant table is very slow

Hi,

To get an idea of the break-down of rows per partition etc. can you please run the following?

It will do a full table scan but will only return a single row.

SELECT DATE,SUM(AMP_ROWCOUNT) AS ROWCOUNT
      ,MAX(AMP_ROWCOUNT) AS MAX_AMP_ROWCOUNT
      ,MIN(AMP_ROWCOUNT) AS MIN_AMP_ROWCOUNT
      ,100.00 * AVG(AMP_ROWCOUNT) / NULLIF(MAX(AMP_ROWCOUNT),0) AS ROW_PAR_EFF
     ,COUNT(DISTINCT(AMPNO)) AS NUMBER_OF_AMPS
     ,MAX(MAX_RHP_ROWCOUNT) AS MAX_RHP_ROWCOUNT
     ,MIN(MIN_RHP_ROWCOUNT) AS MIN_RHP_ROWCOUNT
      ,100.00 * AVG(AVG_RHP_ROWCOUNT) / NULLIF(MAX(MAX_RHP_ROWCOUNT),0) AS ROW_RHP_PAR_EFF
FROM (SELECT HASHAMP(HASHBUCKET(HR_NO)) AS AMPNO
            ,SUM(ROWCOUNT) AS AMP_ROWCOUNT
             ,MAX(ROWCOUNT) AS MAX_RHP_ROWCOUNT
             ,MIN(ROWCOUNT) AS MIN_RHP_ROWCOUNT
             ,AVG(ROWCOUNT) AS AVG_RHP_ROWCOUNT
      FROM (SELECT PARTITION,HASHROW(ar_id) AS HR_NO
                  ,COUNT(*) AS ROWCOUNT
            FROM original_table
            GROUP BY 1,2
           ) AS DT_HR
      GROUP BY 1
      ) AS DT_AMP
;

This will do a full table scan but it wil only return a single row.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: Copying giant table is very slow

A couple additional thoughts along the same lines as the latest posts from Todd & Dave:

  • With BLC, it's possible for row count to be skewed without much difference in PermSpace, particularly if the skewing places data rows with very similar contents into the same blocks, so they compress particularly well. (It's also possible for PermSpace to be skewed even if row count is similar.)
  • Since this is a 2690, decompression/compression will be done by dedicated hardware, and not reflected in AMP CPU consumption.