SQL View Module Conclusion

1. A view is not a physical copy of data and does not contain any data itself. A view is merely a logical overlay of existing tables. Every execution against a view executes the query contained within the view against all underlying tables. The danger with using views is filtering a query against a view, expecting to read a very small portion of a very large table. Any filtering should be done within the view because any filtering against the view itself is applied after the query in the view has completed execution. Views are typically useful for speeding up the development process but in the long run can completely kill database performance.
2. Materialized views: Materialized views are available in some very large capacity type relational databases. A materialized view materializes the underlying physical data by making a physical copy of data from tables. So, unlike a view, when a query is executed against a materialized view, the materialized view is physically accessed rather than the underlying tables. The objective is to free the underlying tables for other uses, effectively creating two separate physical copies. Materialized views are often used to aggregate large data sets down to smaller sized data sets, in data warehouses and data marts.^[2]
   The biggest potential problem with materialized views is how often they are refreshed and brought up to date with any changes to their underlying tables. Another attribute of materialized views is the ability of some database engines to allow a query directed at an underlying table to be automatically redirected to a physically much smaller materialized view, sometimes called automated query rewrite. Queries can be automatically rewritten by the query Optimizer if the query rewrite can help to increase query performance.
3. Clusters: Clusters are used in very few databases and have been somewhat superceded by materialized views. In the past, clusters were used to pre-create physical copies of entire field level sections of heavily accessed tables, especially in SQL joins. Unlike materialized views, clusters do not allow for automatic refresh and are normally manually maintained.
4. Sequences and auto counters: An auto counter field is a special datatype, sometimes called a non-static internal function, allowing automated generation of sequential number values (thus the term sequence). Typically, auto counters are used for primary key surrogate key generation on insertion of new records into a table.
5. Partitioning and parallel processing: Some databases allow physical splitting of tables into separate partitions, including parallel processing on multiple partitions and individual operations on individual partitions. One particularly efficient aspect of partitioning is the capability when querying a table to read fewer than all the partitions making up a table, perhaps even a single partition.

SQL Views - Quiz