Hour 20. Creating and Using Views and Synonyms
What You’ll Learn in This Hour:
• What views are
• How views are used
• Views and security
• Storing views
• Creating views
• Joining views
• Data manipulation in a view
• Performance of nested views
• What synonyms are
• Managing synonyms
• Creating synonyms
• Dropping synonyms
In this hour, you learn about performance, as well as how to create and drop views, how to use views for security, and how to provide simplicity in data retrieval for end users and reports. This hour also includes a discussion on synonyms.
What Is a View?
A view is a virtual table. That is, a view looks like a table and acts like a table as far as a user is concerned, but it doesn’t require physical storage. A view is actually a composition of a table in the form of a predefined query, which is stored in the database. For example, you can create a view from EMPLOYEE_TBL that contains only the employee’s name and address, instead of all columns in EMPLOYEE_TBL. A view can contain all rows of a table or select rows from a table. You can create a view from one or many tables.
When you create a view, a SELECT statement is actually run against the database, which defines the view. The SELECT statement that defines the view might simply contain column names from the table, or it can be more explicitly written using various functions and calculations to manipulate or summarize the data that the user sees. Study the illustration of a view in Figure 20.1.
A view is considered a database object, although the view takes up no storage space on its own. The main difference between a view and a table is that data in a table consumes physical storage, whereas a view does not require physical storage because it is actually referring to data from a table.
A view is used in the same manner that a table is used in the database, meaning that data can be selected from a view as it is from a table. Data can also be manipulated in a view, although there are some restrictions. The following sections discuss some common uses for views and how they are stored in the database.
Watch Out!: Dropping Tables Used by Views
If a table that created a view is dropped, the view becomes inaccessible. You receive an error when trying to query against the view.
Utilizing Views to Simplify Data Access
Sometimes, through the process of normalizing your database or just as a process of database design, the data might be contained in a table format that does not easily lend itself to querying by end users. In this instance, you could create a series of views to make the data simpler for your end users to query. Your users might need to query the employee salary information from the learnsql database. However, they might not totally understand how to create joins between EMPLOYEE_TBL and EMPLOYEE_PAY_TBL. To bridge this gap, you create a view that contains the join and gives the end users the right to select from the view.
Utilizing Views as a Form of Security
Did You Know?: Views Can Be Used as a Form of Security
Views can restrict user access to particular columns in a table or to rows in a table that meet specific conditions as defined in the WHERE clause of the view definition.
Views can be utilized as a form of security in the database. Let’s say you have a table called EMPLOYEE_TBL. EMPLOYEE_TBL includes employee names, addresses, phone numbers, emergency contacts, department, position, and salary or hourly pay. You have some temporary help come in to write a report of employees’ names, addresses, and phone numbers. If you give access to EMPLOYEE_TBL to the temporary help, they can see how much each of your employees receives in compensation—you do not want this to happen. To prevent that, you have created a view containing only the required information: employee name, address, and phone numbers. You can then give the temporary help access to the view to write the report without giving them access to the compensation columns in the table.
Utilizing Views to Maintain Summarized Data
If you have a summarized data report in which the data in the table or tables is updated often and the report is created often, a view with summarized data might be an excellent choice.
For example, suppose that you have a table containing information about individuals, such as city of residence, gender, salary, and age. You could create a view based on the table that shows summarized figures for individuals for each city, such as the average age, average salary, total number of males, and total number of females. To retrieve this information from the base table(s) after the view is created, you can simply query the view instead of composing a SELECT statement that might, in some cases, turn out to be very complex.
The only difference between the syntax for creating a view with summarized data and creating a view from a single or multiple tables is the use of aggregate functions. Review Hour 9, “Summarizing Data Results from a Query,” for the use of aggregate functions.
A view is stored in memory only. It takes up no storage space—as do other database objects—other than the space required to store the view definition. The view’s creator or the schema owner owns the view. The view owner automatically has all applicable privileges on that view and can grant privileges on the view to other users, as with tables. The GRANT command’s GRANT OPTION privilege works the same as on a table. See Hour 19, “Managing Database Security,” for more information.
Creating Views
Views are created using the CREATE VIEW statement. You can create views from a single table, multiple tables, or another view. To create a view, a user must have the appropriate system privilege according to the specific implementation.
The basic CREATE VIEW syntax is as follows:
CREATE [RECURSIVE]VIEW VIEW_NAME
[COLUMN NAME [,COLUMN NAME]]
[OF UDT NAME [UNDER TABLE NAME]
[REF IS COLUMN NAME SYSTEM GENERATED |USER GENERATED | DERIVED]
[COLUMN NAME WITH OPTIONS SCOPE TABLE NAME]]
AS
{SELECT STATEMENT}
[WITH [CASCADED | LOCAL] CHECK OPTION]
The following subsections explore different methods for creating views using the CREATE VIEW statement.
Did You Know?: ANSI SQL Has No ALTER VIEW Statement
There is no provision for an ALTER VIEW statement in ANSI SQL, although most database implementations do provide for that capability. For example, in older versions of MySQL, you use REPLACE VIEW to alter a current view. However, the newest versions of MySQL, SQL Server, and Oracle support the ALTER VIEW statement. Check with your specific database implementation’s documentation to see what is supported.
Creating a View from a Single Table
You can create a view from a single table.
CREATE VIEW VIEW_NAME AS
SELECT * | COLUMN1 [, COLUMN2 ]
FROM TABLE_NAME
[ WHERE EXPRESSION1 [, EXPRESSION2 ]]
[ WITH CHECK OPTION ]
[ GROUP BY ]
The simplest form for creating a view is one based on the entire contents of a single table, as in the following example:
CREATE VIEW CUSTOMERS_VIEW AS
SELECT *
FROM CUSTOMER_TBL;
View created.
The next example narrows the contents for a view by selecting only specified columns from the base table:
CREATE VIEW EMP_VIEW AS
SELECT LAST_NAME, FIRST_NAME, MIDDLE_NAME
FROM EMPLOYEE_TBL;
View created.
The following is an example of how columns from the base table can be combined or manipulated to form a column in a view. The view column is titled NAMES by using an alias in the SELECT clause.
CREATE VIEW NAMES AS
SELECT LAST_NAME || ', ' ||FIRST_NAME || ' ' || MIDDLE_NAME NAME
FROM EMPLOYEE_TBL;
View created.
Now you select all data from the NAMES view that you created:
SELECT *
FROM NAMES;
NAME
-----------------
STEPHENS, TINA D
PLEW, LINDA C
GLASS, BRANDON S
GLASS, JACOB
WALLACE, MARIAH
SPURGEON, TIFFANY
6 rows selected.
The following example shows how to create a view with summarized data from one or more underlying tables:
CREATE VIEW CITY_PAY AS
SELECT E.CITY, AVG(P PAY_RATE) AVG_PAY
FROM EMPLOYEE_TBL E,
EMPLOYEE_PAY_TBL P
WHERE E.EMP_ID = P.EMP_ID
GROUP BY E.CITY;
View created.
Now you can select from your summarized view:
SELECT *
FROM CITY_PAY;
CITY AVG_PAY
--------------- -------
GREENWOOD
INDIANAPOLIS 13.33333
WHITELAND
3 rows selected.
By summarizing a view, SELECT statements that might occur in the future are simplified against the underlying table of the view.
Creating a View from Multiple Tables
You can create a view from multiple tables by using a JOIN in the SELECT statement. The syntax is as follows:
CREATE VIEW VIEW_NAME AS
SELECT * | COLUMN1 [, COLUMN2 ]
FROM TABLE_NAME1, TABLE_NAME2 [, TABLE_NAME3 ]
WHERE TABLE_NAME1 = TABLE_NAME2
[ AND TABLE_NAME1 = TABLE_NAME3 ]
[ EXPRESSION1 ][, EXPRESSION2 ]
[ WITH CHECK OPTION ]
[ GROUP BY ]
The following is an example of creating a view from multiple tables:
CREATE VIEW EMPLOYEE_SUMMARY AS
SELECT E.EMP_ID, E.LAST_NAME, P.POSITION, P.DATE_HIRE, P.PAY_RATE
FROM EMPLOYEE_TBL E,
EMPLOYEE PAY_TBL P
WHERE E.EMP_ID = P.EMP_ID;
View created.
Remember that when selecting data from multiple tables, the tables must be joined by common columns in the WHERE clause. A view is nothing more than a SELECT statement; therefore, tables are joined in a view definition the same as they are in a regular SELECT statement. Recall the use of table aliases to simplify the readability of a multiple-table query.
A view can also be joined with tables and with other views. The same principles apply to joining views with tables and other views that apply to joining tables to other tables. Review Hour 13, “Joining Tables in Queries,” for more information.
Creating a View from a View
You can create a view from another view using the following format:
CREATE VIEW2 AS
SELECT * FROM VIEW1
You can create a view from a view many layers deep (a view of a view of a view, and so on). How deep you can go is implementation specific. The only problem with creating views based on other views is their manageability. For example, suppose that you create VIEW2 based on VIEW1 and then create VIEW3 based on VIEW2. If VIEW1 is dropped, VIEW2 and VIEW3 are no good. The underlying information that supports these views no longer exists. Therefore, always maintain a good understanding of the views in the database and on which other objects those views rely (see Figure 20.2).
Figure 20.2. View dependencies.
Figure 20.2 shows the relationship of views that are dependent not only on tables, but on other views. VIEW1 and VIEW2 are dependent on the TABLE. VIEW3 is dependent on VIEW1. VIEW4 is dependent on both VIEW1 and VIEW2. VIEW5 is dependent on VIEW2. Based on these relationships, the following can be concluded:
• If VIEW1 is dropped, VIEW3 and VIEW4 are invalid.
• If VIEW2 is dropped, VIEW4 and VIEW5 are invalid.
• If the TABLE is dropped, none of the views is valid.
By the Way: Choose Carefully How You Implement Your Views
If a view is as easy and efficient to create from the base table as from another view, preference should go to the view being created from the base table.
WITH CHECK OPTION
WITH CHECK OPTION is a CREATE VIEW statement option. The purpose of WITH CHECK OPTION is to ensure that all UPDATE and INSERT commands satisfy the condition(s) in the view definition. If they do not satisfy the condition(s), the UPDATE or INSERT returns an error. WITH CHECK OPTION actually enforces referential integrity by checking the view’s definition to see that it is not violated.
The following is an example of creating a view with WITH CHECK OPTION:
CREATE VIEW EMPLOYEE_PAGERS AS
SELECT LAST_NAME, FIRST_NAME, PAGER
FROM EMPLOYEE_TBL
WHERE PAGER IS NOT NULL
WITH CHECK OPTION;
View created.
WITH CHECK OPTION in this case should deny the entry of any NULL values in the view’s PAGER column because the view is defined by data that does not have a NULL value in the PAGER column.
Try to insert a NULL value into the PAGER column:
INSERT INTO EMPLOYEE PAGERS
VALUES ('SMITH','JOHN',NULL);
insert into employee_pagers
*
ERROR at line 1:
ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert
When you choose to use WITH CHECK OPTION during creation of a view from a view, you have two options: CASCADED and LOCAL. CASCADED is the default and is assumed if neither is specified. CASCADED is the ANSI standard for the syntax, however, Microsoft SQL Server and Oracle use the slightly different keyword CASCADE. The CASCADED option checks all underlying views, all integrity constraints during an update for the base table, and against defining conditions in the second view. The LOCAL option checks only integrity constraints against both views and the defining conditions in the second view, not the underlying base table. Therefore, it is safer to create views with the CASCADED option because the base table’s referential integrity is preserved.
Creating a Table from a View
You can create a table from a view, just as you can create a table from another table (or a view from another view).
The syntax is as follows:
CREATE TABLE TABLE_NAME AS
SELECT {* | COLUMN1 [, COLUMN2 ]
FROM VIEW_NAME
[ WHERE CONDITION1 [, CONDITION2 ]
[ ORDER BY ]
By the Way: Subtle Differences Between Tables and Views
Remember that the main difference between a table and a view is that a table contains actual data and consumes physical storage, whereas a view contains no data and requires no storage other than to store the view definition (the query).
First, create a view based on two tables:
CREATE VIEW ACTIVE_CUSTOMERS AS
SELECT C.*
FROM CUSTOMER_TBL C,
ORDERS_TBL O
WHERE C.CUST_ID = O.CUST_ID;
View created.
Next, create a table based on the previously created view:
CREATE TABLE CUSTOMER_ROSTER_TBL AS
SELECT CUST_ID, CUST_NAME
FROM ACTIVE_CUSTOMERS;
Table created.
Did You Know?: Defer the Use of the GROUP BY Clause in Your Views
Using the ORDER BY clause in the SELECT statement that is querying the view is better and simpler than using the GROUP BY clause in the CREATE VIEW statement.
Finally, select data from the table, the same as any other table:
SELECT *
FROM CUSTOMER_ROSTER_TBL;
CUST_ID CUST_NAME
---------- ------------------
232 LESLIE GLEASON
12 MARYS GIFT SHOP
43 SCHYLERS NOVELTIES
090 WENDY WOLF
287 GAVINS PLACE
432 SCOTTYS MARKET
6 rows selected.
Views and the ORDER BY Clause
You cannot use the ORDER BY clause in the CREATE VIEW statement; however, the GROUP BY clause has the same effect as an ORDER BY clause when it’s used in the CREATE VIEW statement.
The following is an example of a GROUP BY clause in a CREATE VIEW statement:
CREATE VIEW NAMES2 AS
SELECT LAST_NAME || ', ' || FIRST_NAME || ' ' ||MIDDLE_NAME NAME
FROM EMPLOYEE_TBL
GROUP BY LAST_NAME || ', ' || FIRST_NAME || ' ' || MIDDLE_NAME;
View created.
If you select all data from the view, the data is in alphabetical order (because you grouped by NAME):
SELECT *
FROM NAMES2;
NAME
-----------------
GLASS, BRANDON S
GLASS, JACOB
PLEW, LINDA C
SPURGEON, TIFFANY
STEPHENS, TINA D
WALLACE, MARIAH
6 rows selected.
Updating Data Through a View
You can update the underlying data of a view under certain conditions:
• The view must not involve joins.
• The view must not contain a GROUP BY clause.
• The view must not contain a UNION statement.
• The view cannot contain a reference to the pseudocolumn ROWNUM.
• The view cannot contain group functions.
• The DISTINCT clause cannot be used.
• The WHERE clause cannot include a nested table expression that includes a reference to the same table as referenced in the FROM clause.
• This means that the view can perform INSERTS, UPDATES, and DELETES as long as they honor these caveats.
Review Hour 14, “Using Subqueries to Define Unknown Data,” for the UPDATE command’s syntax.
Dropping a View
You use the DROP VIEW command to drop a view from the database. The two options for the DROP VIEW command are RESTRICT and CASCADE. If a view is dropped with the RESTRICT option and other views are referenced in a constraint, the DROP VIEW errs. If the CASCADE option is used and another view or constraint is referenced, the DROP VIEW succeeds and the underlying view or constraint is dropped. An example follows:
DROP VIEW NAMES2;
View dropped.
Performance Impact of Using Nested Views
Views adhere to the same performance characteristics as tables when they are used in queries. As such, you need to be cognizant of the fact that hiding complex logic behind a view does not negate the fact that the data must be parsed and assembled by the system querying the underlying tables. Views must be treated as any other SQL statement in terms of performance tuning. If the query that makes up your view is not preformant, the view itself experiences performance issues.
Additionally, some users employ views to break down complex queries into multiple units of views and views that are created on top of other views. Although this might seem to be an excellent idea to break down the logic into simpler steps, it can present some performance degradation. This is because the query engine must break down and translate each sublayer of view to determine what exactly it needs to do for the query request.
The more layers you have, the more the query engine has to work to come up with an execution plan. In fact, most query engines do not guarantee that you get the best overall plan but merely that you get a decent plan in the shortest amount of time. So it is always best practice to keep the levels of code in your query as flat as possible and to test and tune the statements that make up your views.
By the Way: Synonyms Are Not ANSI SQL Standard
Synonyms are not American National Standards Institute (ANSI) SQL standard; however, because several major implementations use synonyms, it is best we discuss them briefly here. You must check your particular implementation for the exact use of synonyms, if available. Note, however, that MySQL does not support synonyms. However, you might be able to implement the same type of functionality using a view instead.
What Is a Synonym?
A synonym is merely another name for a table or a view. Synonyms are usually created so a user can avoid having to qualify another user’s table or view to access the table or view. Synonyms can be created as PUBLIC or PRIVATE. Any user of the database can use a PUBLIC synonym; only the owner of a database and any users that have been granted privileges can use a PRIVATE synonym.
Either a database administrator (or another designated individual) or individual users manage synonyms. Because there are two types of synonyms, PUBLIC and PRIVATE, different system-level privileges might be required to create one or the other. All users can generally create a PRIVATE synonym. Typically, only a DBA or privileged database user can create a PUBLIC synonym. Refer to your specific implementation for required privileges when creating synonyms.
Creating Synonyms
The general syntax to create a synonym is as follows:
CREATE [PUBLIC|PRIVATE] SYNONYM SYNONYM_NAME FOR TABLE|VIEW
You create a synonym called CUST, short for CUSTOMER_TBL, in the following example. This frees you from having to spell out the full table name.
CREATE SYNONYM CUST FOR CUSTOMER_TBL;
Synonym created.
SELECT CUST_NAME
FROM CUST;
CUST_NAME
----------------------------
LESLIE GLEASON
NANCY BUNKER
ANGELA DOBKO
WENDY WOLF
MARYS GIFT SHOP
SCOTTYS MARKET
JASONS AND DALLAS GOODIES
MORGANS CANDIES AND TREATS
SCHYLERS NOVELTIES
GAVINS PLACE
HOLLYS GAMEARAMA
HEATHERS FEATHERS AND THINGS
RAGANS HOBBIES INC
ANDYS CANDIES
RYANS STUFF
15 rows selected.
It is also common for a table owner to create a synonym for the table to which you have been granted access so you do not have to qualify the table name by the name of the owner:
CREATE SYNONYM PRODUCTS_TBL FOR USER1.PRODUCTS_TBL;
Synonym created.
Dropping Synonyms
Dropping synonyms is like dropping almost any other database object. The general syntax to drop a synonym is as follows:
DROP [PUBLIC|PRIVATE] SYNONYM SYNONYM_NAME
The following is an example:
DROP SYNONYM CUST;
Synonym dropped.
Summary
This hour discusses two important features in SQL: views and synonyms. In many cases, these features are not used when they could aid in the overall functionality of relational database users. Views were defined as virtual tables—objects that look and act like tables but do not take physical space like tables. Views are actually defined by queries against tables and possible other views in the database. Administrators typically use views to restrict data that a user sees and to simplify and summarize data. You can create views from views, but take care not to embed views too deeply to avoid losing control over their management. There are various options when creating views; some are implementation specific.
Synonyms are objects in the database that represent other objects. They simplify the name of another object in the database, either by creating a synonym with a short name for an object with a long name or by creating a synonym on an object owned by another user to which you have access. There are two types of synonyms: PUBLIC and PRIVATE. A PUBLIC synonym is one that is accessible to all database users, whereas a PRIVATE synonym is accessible to a single user. A DBA typically creates a PUBLIC synonym, whereas each user normally creates her own PRIVATE synonyms.
Q&A
Q. How can a view contain data but take no storage space?
A. A view does not contain data. A view is a virtual table or a stored query. The only space required for a view is for the actual view creation statement, called the view definition.
Q. What happens to the view if a table from which a view was created is dropped?
A. The view is invalid because the underlying data for the view no longer exists.
Q. What are the limits on naming the synonym when creating synonyms?
A. This is implementation specific. However, the naming convention for synonyms in most major implementations follows the same rules that apply to the tables and other objects in the database.
Workshop
The following workshop is composed of a series of quiz questions and practical exercises. The quiz questions are designed to test your overall understanding of the current material. The practical exercises are intended to afford you the opportunity to apply the concepts discussed during the current hour, as well as build upon the knowledge acquired in previous hours of study. Please take time to complete the quiz questions and exercises before continuing. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers.
Quiz
1. Can you delete a row of data from a view that you created from multiple tables?
2. When creating a table, the owner is automatically granted the appropriate privileges on that table. Is this true when creating a view?
3. Which clause orders data when creating a view?
4. Which option can you use when creating a view from a view to check integrity constraints?
5. You try to drop a view and receive an error because of one or more underlying views. What must you do to drop the view?
Exercises
1. Write a statement to create a view based on the total contents of EMPLOYEE_TBL.
2. Write a statement that creates a summarized view containing the average pay rate and average salary for each city in EMPLOYEE_TBL.
3. Create another view for the same summarized data, except use the view you created in Exercise 1 instead of the base EMPLOYEE_TBL. Compare the two results.
4. Use the view in Exercise 2 to create a table called EMPLOYEE_PAY_SUMMARIZED. Verify that the view and the table contain the same data.
5. Write a statement that drops the table and the three views that you created.