Sometimes it is handy to be able to get a quick list of when a view was changed last. It could be for any number of reason, but sometimes folks just lose track of when a view was last updated or even need to verify that it hadn’t been changed recently. So here is a quick SQL, which can be dropped in Aginity Workbench for Netezza to create a list of when a view was created or was update dated last. Update the Database name in the SQL and run it.
SQL List of When A view was Last Changed or Created
select t.database — Database
, t.OWNER — Object Owner
, t.VIEWNAME — View Name
, o.objmodified — The Last Modified Datetime
, o.objcreated — Created Datetime
from _V_OBJECT o
o.objid = t.objid
and DATABASE = ‘<<Database Name>>‘
order by o.objcreated Desc, o.objmodified Desc;
If we consider Materialized Views (MV) in their simplest form, as a point in time stored query result, then materialized views serve two primary purposes Performance Optimization and Semantics Simplification.
There are several ways in which materialized views can improve performance:
Reduce Database Workloads: materialized views can reduce database workloads by pre-assembling frequently used queries and, thereby, eliminating the repetitive execution of joins, aggregations, and filtering.
FacilitateDatabase Optimizers: in some databases can be partitioning and indexing which are considered by database optimizers. Also, some databases, in which more than one materialized view has been applied to a table, the database optimizer will consider all the associated materialized views when optimizing queries.
Reduced Network Workloads: by the use of database replication and/or mass deployment techniques to materialized views, they can be distributed to more local proximity to the consumers, thereby, reducing the data volume across the network and provided business continuation/disaster recovery capabilities, should the primary site become temporarily unavailable.
Precalculation and/or Preaggregation: Performing calculation and aggregation of information upon creation of a materialized view, eliminates the need to perform these functions on an on-demand basis as various consumers submit requests.
Data Subsets: by applying filters to eliminate unnecessary data (e.g. history data no longer in common reporting use) or unnecessary data attributes (e.g. unused columns or columns intended for other information purposes) the impact of filter for these items is reduced and is effectively eliminated for consumers of the materialized view.
Materialized views can be used to simplify the semantics provided to information consumers with Ad hoc capabilities and/or to simplify the construction of reporting and analytics objects. Depending on the database and/or integration tools in use to create them, materialized views can simplify the consumer experience by:
Reduce or Eliminate Join Coding: When constructed materialized views can perform the joins and populate the materialized view with the value results of from the join table, thereby, eliminating the need for the consumer to perform this function as an ad hoc user or in the semantics of reporting and analytics tools
Pre-application of Business Rules: When constructed materialized views can apply business rules to facilitate queries by adding indicator flags and pre-applying special business logic to data and populating the materialized view with the value results, thus, eliminating the need for the consumer to perform this function as an ad hoc user or in the semantics of reporting and analytics tools.
Precalculation and/or Pre-aggregation: Performing calculation and aggregation of information upon creation of a materialized view allow the consumer to use the results without the need to build the calculations and/or aggregations into the ad hoc query or in the semantics of reporting and analytics tools. This also helps to ensure information accuracy and consistency.
Data Subsets: By prefiltering the data during the creation of the view unnecessary or unused data and columns are not available to consumers and do not need to filter out of ad hoc queries or in the semantics of reporting and analytics tools