Archives of the TeradataForum

Message Posted: Thu, 07 Apr 2005 @ 17:21:27 GMT

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

That's the most geeked out sql I've ever seen (that's a compliment).

Here's a trimmed down version for row counts. It's just looking at the PI which should have stats collected.

     SELECT databasename, tablename,
         CASE WHEN SUBSTR(Stats, 23, 1) = '00'XB THEN 16 ELSE 0 END AS Offset,
         HASHBUCKET(SUBSTR(Stats, 57+Offset+7, 1)
                  || SUBSTR(Stats, 57+Offset+6, 1) (BYTE(4))) AS NumRowsw1,
         HASHBUCKET(SUBSTR(Stats, 57+Offset+5, 1)
                  || SUBSTR(Stats, 57+Offset+4, 1) (BYTE(4))) AS NumRowsw2,
         HASHBUCKET(SUBSTR(Stats, 57+Offset+3, 1)
                  || SUBSTR(Stats, 57+Offset+2, 1) (BYTE(4))) AS NumRowsw3,
         HASHBUCKET(SUBSTR(Stats, 57+Offset+1, 1)
                  || SUBSTR(Stats, 57+Offset+0, 1) (BYTE(4))) AS NumRowsw4,
          (-1**(NumRowsw1 / 32768))
          * (2**((NumRowsw1/16 MOD 2048) - 1023))
          * (1 + ((NumRowsw1 MOD 16) * 2**-4) + (NumRowsw2 * 2**-20)
               + (NumRowsw3 * 2**-36) + (NumRowsw4 * 2**-52)) (DECIMAL(18,0))
     AS NumRows
     FROM
     (
     SELECT
            d.databasename AS DatabaseName,
            t.tvmname AS TableName,
            SUBSTR(i.IndexStatistics, 1, 80) AS Stats
          FROM
            dbc.dbase d
          JOIN dbc.tvm t
            ON d.databaseid = t.databaseid
          JOIN dbc.tvfields c
            ON t.tvmid = c.tableid
          JOIN dbc.Indexes i
            ON c.tableid = i.tableid
            AND c.fieldid = i.fieldid
          where i.indextype = 'P'
          --and d.databasename = 'dw_data'
          GROUP BY
            DatabaseName,
            TableName,
            i.IndexStatistics
          HAVING Stats IS NOT NULL
      ) statssub

And here's one if you only want the row counts. It's impossible to read though.

  SELECT databasename, tablename,
     (-1**(HASHBUCKET(SUBSTR(Stats, 57+ CASE WHEN SUBSTR(Stats, 23, 1) ='00'XB THEN 16 ELSE 0 END+7, 1)
             || SUBSTR(Stats, 57+ CASE WHEN SUBSTR(Stats, 23, 1) = '00'XB THEN 16 ELSE 0 END+6, 1) (BYTE(4)))/ 32768))
     * (2**((HASHBUCKET(SUBSTR(Stats, 57+ CASE WHEN SUBSTR(Stats, 23, 1) = '00'XB THEN 16 ELSE 0 END+7, 1)
             || SUBSTR(Stats, 57+ CASE WHEN SUBSTR(Stats, 23, 1) = '00'XB THEN 16 ELSE 0 END+6, 1) (BYTE(4)))/16 MOD 2048) - 1023))
     * (1 + ((HASHBUCKET(SUBSTR(Stats, 57+ CASE WHEN SUBSTR(Stats, 23, 1) = '00'XB THEN 16 ELSE 0 END+7, 1)
             || SUBSTR(Stats, 57+ CASE WHEN SUBSTR(Stats, 23, 1) = '00'XB THEN 16 ELSE 0 END+6, 1) (BYTE(4)))MOD 16) * 2**-4)
     + (HASHBUCKET(SUBSTR(Stats, 57+ CASE WHEN SUBSTR(Stats, 23, 1) = '00'XB THEN 16 ELSE 0 END+5, 1)
             || SUBSTR(Stats, 57+ CASE WHEN SUBSTR(Stats, 23, 1) = '00'XB THEN 16 ELSE 0 END+4, 1) (BYTE(4)))* 2**-20)
     + (    HASHBUCKET(SUBSTR(Stats, 57+ CASE WHEN SUBSTR(Stats, 23, 1) = '00'XB THEN 16 ELSE 0 END+3, 1)
             || SUBSTR(Stats, 57+ CASE WHEN SUBSTR(Stats, 23, 1) = '00'XB THEN 16 ELSE 0 END+2, 1) (BYTE(4))) * 2**-36)
     + (    HASHBUCKET(SUBSTR(Stats, 57+ CASE WHEN SUBSTR(Stats, 23, 1) = '00'XB THEN 16 ELSE 0 END+1, 1)
             || SUBSTR(Stats, 57+ CASE WHEN SUBSTR(Stats, 23, 1) = '00'XB THEN 16 ELSE 0 END+0, 1) (BYTE(4)))* 2**-52))
       (DECIMAL(18,0)) AS NumRows
FROM
(
SELECT
       d.databasename AS DatabaseName,
       t.tvmname AS TableName,
       SUBSTR(i.IndexStatistics, 1, 80) AS Stats
     FROM
       dbc.dbase d
     JOIN dbc.tvm t
       ON d.databaseid = t.databaseid
     JOIN dbc.tvfields c
       ON t.tvmid = c.tableid
     JOIN dbc.Indexes i
       ON c.tableid = i.tableid
       AND c.fieldid = i.fieldid
   where i.indextype = 'P'
   -- and d.databasename = 'dw_data'
     GROUP BY
       DatabaseName,
       TableName,
       i.IndexStatistics
     HAVING Stats IS NOT NULL
 ) statssub

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