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.
A couple additional thoughts along the same lines as the latest posts from Todd & Dave: