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