Friday, June 13, 2014

Netezza Versioned Tables

Versioned tables come about as a result of doing an
               ALTER TABLE <tablename> [ADD|DROP] COLUMN ...

          This results in multiple data stores for the table.  When you go to query
          the table, NPS must recombine the separate data stores back into a single
          entity.  This action will be performed automatically and on-the-fly.  But
          it does result in additional query overhead.  Therefore, it is a best
          practice to reconstitute the table as soon as practical by doing a

               GROOM TABLE <tablename> VERSIONS;

          Notes:  The maximum number of table versions allowed is 4 (which means you
                  can perform at most three ALTER TABLE commands before doing a GROOM,
                  since this number includes the original table version itself)


SELECT  the_table.database as "Database",
        MAX
        (   CASE WHEN the_table.objclass = 4910                THEN   SUBSTR(the_table.objname, 3)::nvarchar(128)
                 WHEN the_table.objclass in (4951,4959,4963)   THEN NULL
                 ELSE   the_table.objname::nvarchar(128)
            END
        ) AS "Table Name",
        TO_CHAR ( nvl(SUM ( used_bytes ),0), '999,999,999,999,999,999'  ) AS "Size (Bytes)",
count(distinct(case when objclass in (4959,4963) then objid else null end)) as "# Of Versions"
FROM    _V_OBJ_RELATION_XDB the_table
        left outer join _V_SYS_OBJECT_DSLICE_INFO on
        (
               the_table.objid = _V_SYS_OBJECT_DSLICE_INFO.tblid   and
               _V_SYS_OBJECT_DSLICE_INFO.tblid > 200000
        )
WHERE
            the_table.objclass in (4905,4910,4940,4959,4951,4953,4961,4963)
        and the_table.objid > 200000
GROUP BY
        "Database",
        the_table.visibleid
HAVING  "# Of Versions" >= 2
ORDER BY 1, 2;


Below is sample output:

# Of Versioned Tables         13
     Total # Of Versions      26

       Database        |                       Table Name                       |       Size (Bytes)       | # Of Versions
-----------------------+--------------------------------------------------------+--------------------------+---------------
 CELEBI_BSS_SIT_DDS_DB | SUBSCRIPTION_ACQUISITION_REVENUE_SUMMARY_FULL_11062014 |              566,755,328 |             2
 CELEBI_PREPROD_DDS_DB | COGNOS_CUBE_CONTROL                                    |                2,883,584 |             2
 CELEBI_PREPROD_DDS_DB | COGNOS_CUBE_CONTROL_HIST                               |                  262,144 |             2
 CELEBI_SOR_DB         | REV_GROUP_MASTER                                       |               24,117,248 |             2
 CELEBI_STG_DB         | STG_SAP_CSKU                                           |                  262,144 |             2

No comments:

Post a Comment