SQL View Module conclusion
In this module we had a tour of views and what they are used for.
As you build the queries to support your reports, consider using views for those queries that you refer to over and over again.
Remember the rules if you are creating views to support updates. The view must have a unique identifier that it can reference in each of its underlying tables, and security must allow you sufficient rights to the tables and the associated view.
Views apply to the course project by providing you a way to build a SELECT statement and then save it, making it possible to write the statement, save your work, then reference that statement in your report. You can perfect a given SELECT statement, save it, and not have to think about it again.
So far in this module, topics covered have included tables, relationships between tables, and indexes attached to tables.
You should understand the basic structure of a table, and that the relationships between tables are determined by primary keys in parent tables linked to foreign keys in child tables. Foreign keys are copies of primary key field values from parent tables.
Indexing is important to understand not directly from a modeling perspective, but that indexes are used to superimpose a different order on top of the order created by the very structure of the relationships between tables, imposed by primary and foreign keys.
It is important to know that specialized objects exist as options for expansion to a relational database model, as extensions to both the underlying physical structure of a database and the overlying logical structure (the tables and indexes).
Following are a few examples:
- Views: A view is essentially a query definition and does not contain any data.
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.
- 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.
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.
- 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.
- 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.
- 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
SQL Views - Quiz
A data mart is a subject-oriented database that is often a partitioned segment of an enterprise data warehouse. The subset of data held in a data mart typically aligns with a particular business unit like sales, marketing or real estate.