Hour 20
Creating and Using Views and Synonyms
What You’ll Learn in This Hour:
▶ Defining views and seeing how they are used
▶ Enhancing security with views
▶ Storing, creating, and joining views
▶ Manipulating data in a view
▶ Using nested views
▶ Managing 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.
Defining Views
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 that is stored in the database. For example, you can create a view from BIRDS that contains only the BIRD_NAME and WINGSPAN columns instead of all the columns in BIRDS. A view can contain all rows of a table or only certain rows. You can create a view from one or many tables.
When you create a view, a SELECT statement is run against the database and defines the view. The SELECT statement that defines the view might simply contain column names from the table. Alternatively, it can be more explicitly written using various functions and calculations to manipulate or summarize the data that the user sees. Figure 20.1 shows an example view.
FIGURE 20.1
The view
A view is considered a database object, although it is stored in memory only. Unlike other database objects, it takes up no storage space (other than the space required to store the view definition). Either the view’s creator or the schema owner owns the view. The view owner automatically has all applicable privileges on that view and, as with tables, can grant privileges on the view to other users. The GRANT command’s GRANT OPTION privilege works the same as on a table. See Hour 21, “Managing Database Users and Security,” for more information.
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 some restrictions exist. The following sections discuss some common uses for views and how they are stored in the database.
Caution
Dropping Tables Used by Views
If a table that created a view is dropped, the view becomes inaccessible and you receive an error when trying to query against the view.
Using Views to Simplify Data Access
Sometimes your data might be contained in a table format that does not easily lend itself to querying by end users. This can happen through the process of normalizing your database or just as a process of database design. In this instance, you can create a series of views to make the data simpler for your end users to query. For example, your users might need to query the birds, the food they eat, and the migration location, but they might not totally understand how to create joins between BIRDS, FOODS, and MIGRATION. To bridge this gap, you can create a view that contains the join and gives the end users the right to select from the view.
Using Views As a Form of Security
Views can act as a form of security in the database. Say you have a table called BIRD_RESCUES_STAFF that contains a variety of information about individuals who work for and volunteer in bird rescue facilities, but it also includes pay information. You do not want all users to see the pay information for staff members. You can create a view based on BIRD_RESCUES_STAFF table that excludes the pay information and then give the appropriate users access to that view instead of having them access the base table.
Tip
Views Can Restrict Access to Columns
Views can restrict user access to particular columns or rows in a table that meet specific conditions as defined in the WHERE clause of the view definition.
Using Views to Maintain Summarized Data
Using a view with summarized data is beneficial 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.
For example, suppose that you have a table containing information about individuals, such as city of residence, gender, salary, and age. You can 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 complex.
The only difference in the syntax for creating a view with summarized data and the syntax for creating a view from a single table or multiple tables is the use of aggregate functions. Review Hour 17, “Summarizing Data Results from a Query,” for the use of aggregate functions.
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 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.
Tip
ANSI SQL Has No ALTER VIEW Statement
ANSI SQL has no provision for an ALTER VIEW statement, 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 it supports.
Creating a View from a Single Table
You can create a view from a single table. The syntax follows:
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 based on the entire contents of a single table, as in the following example. First, you select bird names and their associated wingspans from the BIRDS table for birds with a wingspan greater than 48 inches. This is the base query that you use to create the view.
SQL> select bird_id, bird_name, wingspan
2 from birds
3 where wingspan > 48;
BIRD_ID BIRD_NAME WINGSPAN
---------- ------------------------------ ----------
1 Great Blue Heron 78
3 Common Loon 99
4 Bald Eagle 99
5 Golden Eagle 99
7 Osprey 72
9 Canadian Goose 99
13 Ring-billed Gull 50
14 Double-crested Cormorant 54
16 Turkey Vulture 72
19 Mute Swan 99
20 Brown Pelican 99
21 Great Egret 67.2
12 rows selected.
Now you create a view using the previous query. The query is simply substituted into the CREATE VIEW statement and you can see that the view was created successfully.
SQL> create view big_birds_v as 2 select bird_id, bird_name, wingspan 3 from birds 4 where wingspan > 48; View created.
Now if you select all records from the view that was just created, the results are exactly the same as the standalone query that was executed before you created the view. This view is not a table; it is a virtual table. Remember that a view does not actually contain data; it is only a reflection of the data or a subset of data from one or more tables in the database.
SQL> select * from big_birds_v;
BIRD_ID BIRD_NAME WINGSPAN
---------- ------------------------------ ----------
1 Great Blue Heron 78
3 Common Loon 99
4 Bald Eagle 99
5 Golden Eagle 99
7 Osprey 72
9 Canadian Goose 99
13 Ring-billed Gull 50
14 Double-crested Cormorant 54
16 Turkey Vulture 72
19 Mute Swan 99
20 Brown Pelican 99
21 Great Egret 67.2
12 rows selected.
Creating a View from Multiple Tables
You can create a view from multiple tables by using a JOIN in the SELECT statement. The syntax 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 ]
First, you query the BIRDS table. You can see that there are 23 rows of data in the table.
SQL> select bird_name 2 from birds; BIRD_NAME ------------------------------ American Coot American Crow Anhinga Bald Eagle Belted Kingfisher Black Skimmer Brown Pelican Canadian Goose Common Loon Common Merganser Common Sea Gull Double-crested Cormorant Golden Eagle Great Blue Heron Great Egret Green Heron Mallard Mute Swan Osprey Pied-billed Grebe Red Tailed Hawk Ring-billed Gull Turkey Vulture 23 rows selected.
The following query selects the bird’s name and the food item that each bird eats. Three tables are joined together in the query. In this query, only birds that eat fish are returned in the result set.
SQL> select b.bird_id, b.bird_name, f.food_name
2 from birds b,
3 birds_food bf,
4 food f
5 where b.bird_id = bf.bird_id
6 and f.food_id = bf.food_id
7 and f.food_name = 'Fish';
BIRD_ID BIRD_NAME FOOD_NAME
---------- ------------------------------ ------------------------
1 Great Blue Heron Fish
3 Common Loon Fish
4 Bald Eagle Fish
5 Golden Eagle Fish
7 Osprey Fish
8 Belted Kingfisher Fish
12 Common Sea Gull Fish
13 Ring-billed Gull Fish
14 Double-crested Cormorant Fish
15 Common Merganser Fish
17 American Crow Fish
18 Green Heron Fish
20 Brown Pelican Fish
21 Great Egret Fish
22 Anhinga Fish
23 Black Skimmer Fish
16 rows selected.
Now you use this query to create a view called FISH_EATERS. This view is a virtual table that contains data from the BIRDS table and the FOOD table for only birds that eat fish.
SQL> create view fish_eaters as 2 select b.bird_id, b.bird_name 3 from birds b, 4 birds_food bf, 5 food f 6 where b.bird_id = bf.bird_id 7 and f.food_id = bf.food_id 8 and f.food_name = 'Fish'; View created.
If you select all the records from the FISH_EATERS view, you can see that only 16 rows of data are returned, not the original 23 records. This tells you that 16 birds in the database eat fish as part of their diet.
SQL> select *
2 from fish_eaters;
BIRD_ID BIRD_NAME
---------- ------------------------------
1 Great Blue Heron
3 Common Loon
4 Bald Eagle
5 Golden Eagle
7 Osprey
8 Belted Kingfisher
12 Common Sea Gull
13 Ring-billed Gull
14 Double-crested Cormorant
15 Common Merganser
17 American Crow
18 Green Heron
20 Brown Pelican
21 Great Egret
22 Anhinga
23 Black Skimmer
16 rows selected.
Suppose now that you want to return a list of migration locations and birds that migrate to those locations, but only for fish-eating birds. You can do this in several ways, but using a view is one of the simplest methods. You can also simply join all the appropriate tables together in a single query. Study the following output:
SQL> select m.migration_location, fe.bird_id, fe.bird_name 2 from fish_eaters fe, 3 birds_migration bm, 4 migration m 5 where fe.bird_id = bm.bird_id 6 and bm.migration_id = m.migration_id 7 order by 1; MIGRATION_LOCATION BIRD_ID BIRD_NAME --------------------------- ---------- ----------------------------- Central America 8 Belted Kingfisher Central America 7 Osprey Central America 18 Green Heron Central America 21 Great Egret Central America 3 Common Loon Central America 14 Double-crested Cormorant Central America 12 Common Sea Gull Central America 15 Common Merganser Central America 1 Great Blue Heron Mexico 7 Osprey Mexico 18 Green Heron Mexico 21 Great Egret Mexico 1 Great Blue Heron Mexico 14 Double-crested Cormorant Mexico 12 Common Sea Gull Mexico 15 Common Merganser Mexico 3 Common Loon Mexico 8 Belted Kingfisher Mexico 13 Ring-billed Gull Mexico 22 Anhinga No Significant Migration 17 American Crow No Significant Migration 5 Golden Eagle No Significant Migration 20 Brown Pelican No Significant Migration 23 Black Skimmer South America 8 Belted Kingfisher South America 7 Osprey South America 18 Green Heron South America 1 Great Blue Heron South America 12 Common Sea Gull Southern United States 12 Common Sea Gull Southern United States 14 Double-crested Cormorant Southern United States 1 Great Blue Heron Southern United States 7 Osprey Southern United States 18 Green Heron Southern United States 21 Great Egret Southern United States 15 Common Merganser Southern United States 13 Ring-billed Gull Southern United States 8 Belted Kingfisher Southern United States 4 Bald Eagle Southern United States 22 Anhinga Southern United States 3 Common Loon 41 rows selected.
You can see that the previous query returned 41 rows of data. You use the ORDER BY clause to sort the data so that you can easily see which values for migration locations and birds, if any within this data set, repeat. So many rows are returned because multiple birds migrate to multiple locations.
In the next query, you are essentially looking at the same information in a different way, but you return only the distinct value of each migration location that is associated with fish-eating birds. You achieve this by selecting the MIGRATION_LOCATION column only and using the WHERE clause to look for migration locations in a subquery that joins the BIRDS_MIGRATION table to the FISH_EATERS view. This is an example of using a view in a subquery and seeing how to use a view to drill down into the data you need.
SQL> select migration_location 2 from migration 3 where migration_id in (select bm.migration_id 4 from birds_migration bm, 5 fish_eaters fe 6 where bm.bird_id = fe.bird_id); MIGRATION_LOCATION ------------------------------ Southern United States Mexico Central America South America No Significant Migration 5 rows selected.
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 other views. The same principles apply to joining views with tables and other views as when joining tables to other tables. Review Hour 14, “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 which other objects those views rely on (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 also 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
VIEW1is dropped,VIEW3andVIEW4are invalid.▶ If
VIEW2is dropped,VIEW4andVIEW5are invalid.▶ If the
TABLEis dropped, none of the views is valid.
To set up the next example, take a look at all the rows of data in the view SMALL_BIRDS_V. This view has 13 birds that are considered small, according to the conditions provided in the view definition.
SQL> select * from small_birds_v;
BIRD_ID BIRD_NAME WINGSPAN
---------- ------------------------------ ----------
2 Mallard 3.2
6 Red Tailed Hawk 48
8 Belted Kingfisher 23
10 Pied-billed Grebe 6.5
11 American Coot 29
12 Common Sea Gull 18
13 Ring-billed Gull 50
14 Double-crested Cormorant 54
15 Common Merganser 34
17 American Crow 39.6
18 Green Heron 26.8
22 Anhinga 42
23 Black Skimmer 15
13 rows selected.
You can select the MIGRATION_LOCATION from the MIGRATION table and join with the SMALL_BIRDS_V view to return a list of migration locations that are associated with small birds.
SQL> select m.migration_location 2 from migration m, 3 birds_migration bm, 4 small_birds_v sb 5 where m.migration_id = bm.migration_id 6 and bm.bird_id = sb.bird_id 7 order by 1; MIGRATION_LOCATION ------------------------------ Central America Central America Central America Central America Central America Central America Central America Mexico Mexico Mexico Mexico Mexico Mexico Mexico Mexico Mexico No Significant Migration No Significant Migration South America South America South America Southern United States Southern United States Southern United States Southern United States Southern United States Southern United States Southern United States Southern United States Southern United States Southern United States Southern United States 32 rows selected.
The following query is the same as the previous query, except that the DISTINCT function has been applied to the MIGRATION_LOCATION column to show only distinct values of the migration location. In this case, you don’t care how many birds migrate to each location; you just want a list of locations associated with small birds.
SQL> select distinct(m.migration_location) "MIGRATION LOCATION" 2 from migration m, 3 birds_migration bm, 4 small_birds_v sb 5 where m.migration_id = bm.migration_id 6 and bm.bird_id = sb.bird_id; MIGRATION LOCATION ------------------------------ Southern United States Central America South America Mexico No Significant Migration 5 rows selected.
Once again, let’s look at the data in the SMALL_BIRDS_V view.
SQL> select * from small_birds_v;
BIRD_ID BIRD_NAME WINGSPAN
---------- ------------------------------ ----------
2 Mallard 3.2
6 Red Tailed Hawk 48
8 Belted Kingfisher 23
10 Pied-billed Grebe 6.5
11 American Coot 29
12 Common Sea Gull 18
13 Ring-billed Gull 50
14 Double-crested Cormorant 54
15 Common Merganser 34
17 American Crow 39.6
18 Green Heron 26.8
22 Anhinga 42
23 Black Skimmer 15
13 rows selected.
The following example shows a new view created called SMALLEST_BIRDS_V that is based on the data in the SMALL_BIRDS_V view, but only for birds that have a wingspan of less than the average wingspan in the SMALL_BIRDS table that was created earlier.
SQL> create view smallest_birds_v as 2 select * from small_birds_v 3 where wingspan < (select avg(wingspan) 4 from small_birds); View created.
Now if you select data from the most recent view created, SMALLEST_BIRDS_V, you see that the view reflects five rows of data. These records represent the very smallest birds in the database, those that are below average even among the small birds that you defined earlier.
SQL> select * from smallest_birds_v;
BIRD_ID BIRD_NAME WINGSPAN
---------- ------------------------------ ----------
2 Mallard 3.2
8 Belted Kingfisher 23
10 Pied-billed Grebe 6.5
12 Common Sea Gull 18
23 Black Skimmer 15
5 rows selected.
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, give preference to the view created from the base table.
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) in Oracle: by using the CREATE TABLE AS SELECT syntax.
The syntax 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). It’s a subtle difference, yet not so subtle.
The following example creates a table called SMALLEST_BIRDS that is based on a query from the SMALLEST_BIRDS_V view that you just created.
SQL> create table smallest_birds as
2 select * from smallest_birds_v;
Table created.
SQL>
SQL> select * from smallest_birds;
BIRD_ID BIRD_NAME WINGSPAN
---------- ------------------------------ ----------
2 Mallard 3.2
8 Belted Kingfisher 23
10 Pied-billed Grebe 6.5
12 Common Sea Gull 18
23 Black Skimmer 15
5 rows selected.
Incorporating the ORDER BY Clause
Some implementations of SQL enable you to use the ORDER BY clause in the CREATE VIEW statement, whereas others do not. Following is an example of a CREATE VIEW statement that incorporates the ORDER BY clause.
SQL> create view small_birds2_v as
2 select bird_id, bird_name
3 from birds
4 where wingspan < (select avg(wingspan) * .25
5 from birds)
6 order by bird_id, bird_name;
View created.
SQL> select * from small_birds2_v;
BIRD_ID BIRD_NAME
---------- ------------------------------
2 Mallard
10 Pied-billed Grebe
2 rows selected.
Tip
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.
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 BYclause.▶ The view must not contain a
UNIONstatement.▶ The view cannot contain a reference to the pseudocolumn
ROWNUM.▶ The view cannot contain group functions.
▶ The
DISTINCTclause cannot be used.▶ The
WHEREclause cannot include a nested table expression that includes a reference to the same table as referenced in theFROMclause.▶ The view can perform
INSERTS,UPDATES, andDELETESas long as they honor these caveats.
Review Hour 10, “Manipulating Data,” for the UPDATE command’s syntax.
Once again, take a look at the data in the SMALL_BIRDS_V view.
SQL> select * from small_birds_v;
BIRD_ID BIRD_NAME WINGSPAN
---------- ------------------------------ ----------
2 Mallard 3.2
6 Red Tailed Hawk 48
8 Belted Kingfisher 23
10 Pied-billed Grebe 6.5
11 American Coot 29
12 Common Sea Gull 18
13 Ring-billed Gull 50
14 Double-crested Cormorant 54
15 Common Merganser 34
17 American Crow 39.6
18 Green Heron 26.8
22 Anhinga 42
23 Black Skimmer 15
13 rows selected.
The following UPDATE statement changes the bird’s name to Duck for a bird identification of 2.
SQL> update small_birds_v 2 set bird_name = 'Duck' 3 where bird_id = 2; 1 row updated.
Now if you select the data from the view, you can see that the bird name of Mallard shown in the previous output has been changed to Duck. The ROLLBACK command is issued because you do not want that change to remain in the database.
SQL> select * from small_birds_v;
BIRD_ID BIRD_NAME WINGSPAN
---------- ------------------------------ ----------
2 Duck 3.2
6 Red Tailed Hawk 48
8 Belted Kingfisher 23
10 Pied-billed Grebe 6.5
11 American Coot 29
12 Common Sea Gull 18
13 Ring-billed Gull 50
14 Double-crested Cormorant 54
15 Common Merganser 34
17 American Crow 39.6
18 Green Heron 26.8
22 Anhinga 42
23 Black Skimmer 15
13 rows selected.
SQL> rollback;
Rollback complete.
In the following example, an UPDATE statement is attempting to set the height to a null value in the BIRDS table for birds with an identification of 2. An error is returned because, in the base table of BIRDS, the HEIGHT column is defined as a NOT NULL and is a mandatory column. Therefore, the following UPDATE statement violates a constraint that was previously defined in the database. Any data manipulation in a relational database, either directly using a DML command or indirectly through a view or any other type of object, must adhere to the constraints identified previously, to protect the integrity of the data.
SQL> update small_birds_v
2 set height = ''
3 where bird_id = 2;
set height = ''
*
ERROR at line 2:
ORA-01407: cannot update ("RYAN2"."BIRDS"."HEIGHT") to NULL
Using the WITH CHECK Option to Control Data That a View Returns
The WITH CHECK option is a CREATE VIEW statement option. It ensures that all UPDATE and INSERT commands satisfy the condition(s) in the view definition. If the commands do not satisfy the condition(s), the UPDATE or INSERT returns an error. WITH CHECK enforces referential integrity by checking the view’s definition to see that it is not violated.
Following is an example of creating a view with the WITH CHECK option. The small birds view is updated to set the wingspan equal to 72 when the bird name is Mallard. You can see that one row is updated.
SQL> update small_birds_v 2 set wingspan = 72 3 where bird_name = 'Mallard'; 1 row updated.
Now if you issue a query from the SMALL_BIRDS_V view, you can see that the row of data for Mallard no longer exists. This is because you updated the WINGSPAN column to a value that is greater than the values that SMALL_BIRDS_V reflects in the view definition.
SQL> select * from small_birds_v;
BIRD_ID BIRD_NAME WINGSPAN HEIGHT
---------- ------------------------------ ---------- ----------
6 Red Tailed Hawk 48 25
8 Belted Kingfisher 23 13
10 Pied-billed Grebe 6.5 13
11 American Coot 29 16
12 Common Sea Gull 18 18
13 Ring-billed Gull 50 19
14 Double-crested Cormorant 54 33
15 Common Merganser 34 27
17 American Crow 39.6 18
18 Green Heron 26.8 22
22 Anhinga 42 35
23 Black Skimmer 15 20
12 rows selected.
The following example sets the wingspan of the Red Tailed Hawk to 72 inches:
SQL> update small_birds_v 2 set wingspan = 72 3 where bird_name = 'Red Tailed Hawk'; 1 row updated.
Once again, when you look at all the data in the SMALL_BIRDS_V view, you can see that Red Tailed Hawk no longer appears in the output and one fewer row of data is returned.
SQL> select * from small_birds_v;
BIRD_ID BIRD_NAME WINGSPAN HEIGHT
---------- ------------------------------ ---------- ----------
8 Belted Kingfisher 23 13
10 Pied-billed Grebe 6.5 13
11 American Coot 29 16
12 Common Sea Gull 18 18
13 Ring-billed Gull 50 19
14 Double-crested Cormorant 54 33
15 Common Merganser 34 27
17 American Crow 39.6 18
18 Green Heron 26.8 22
22 Anhinga 42 35
23 Black Skimmer 15 20
11 rows selected.
Now let’s drop the view and then re-create the view using the WITH CHECK option. The ROLLBACK command places the record for Red Tailed Hawk back in the view (base table).
SQL> rollback; Rollback complete. SQL> drop view small_birds_v; View dropped.
The next SQL statement creates the same view as before, but using the WITH CHECK option. The WITH CHECK option ensures that any changes to data associated with the view adhere to any criteria used to create the view, regardless of whether underlying constraints exist in the base table.
SQL> SQL> create view small_birds_v as 2 select bird_id, bird_name, wingspan 3 from birds 4 where wingspan < (select avg(wingspan) 5 from birds) 6 with check option; View created.
Now that you have re-created the view using the WITH CHECK option, you can attempt to set the wingspan to 72 inches for the Red Tailed Hawk, just as you did before. However, you can see here that an error is returned. This is because a wingspan of 72 inches is greater than the definition of the view for the WINGSPAN column, which looks for wingspans less than the average wingspan of all birds. The value of 72 inches is greater than the average wingspan of all birds.
SQL> update small_birds_v
2 set wingspan = 72
3 where bird_name = 'Red Tailed Hawk';
update small_birds_v
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
SQL> select * from small_birds_v;
BIRD_ID BIRD_NAME WINGSPAN
---------- ------------------------------ ----------
6 Red Tailed Hawk 48
8 Belted Kingfisher 23
10 Pied-billed Grebe 6.5
11 American Coot 29
12 Common Sea Gull 18
13 Ring-billed Gull 50
14 Double-crested Cormorant 54
15 Common Merganser 34
17 American Crow 39.6
18 Green Heron 26.8
22 Anhinga 42
23 Black Skimmer 15
12 rows selected.
When you choose to use the WITH CHECK option when creating a view from a view, you have two options: CASCADE and LOCAL. CASCADE 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 CASCADE option checks all underlying views, all integrity constraints during an update for the base table, and all 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, creating views is safer with the CASCADE option because it preserves the base table’s referential integrity.
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:
SQL> drop view small_birds_v; View dropped.
Understanding the Performance Impact of Nested Views
Views adhere to the same performance characteristics as tables when they are used in queries. As such, you need to be cognizant that embedding complex logic within a view does not negate that the data must be parsed and assembled by the system querying the underlying tables. However, the use of views can enhance performance by narrowing down data into smaller groups of data that can then be searched and joined with other tables or groups of data. Views must be treated as any other SQL statement for performance tuning. If the query that makes up your view is not performing well, the view itself experiences performance issues.
In addition, some users employ views to break down complex queries into multiple units of data, or views, that are created on top of other views. This might seem to be an excellent way to break down the logic into simpler steps, but it can present some performance degradation: 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; they merely give you a decent plan in the shortest amount of time. 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.
Defining Synonyms
A synonym is another name for a table or a view. Synonyms are usually created so that a user can avoid having to qualify another user’s table or view to access that 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 two types of synonyms (PUBLIC and PRIVATE) are used, different system-level privileges might be required to create them. All users can generally create a PRIVATE synonym. Typically, only a DBA or a privileged database user can create a PUBLIC synonym. Refer to your specific implementation for the required privileges when creating synonyms.
By the Way
Synonyms Are Not ANSI SQL Standard
Synonyms are not ANSI SQL standard; however, because several major implementations use synonyms, this hour discusses them briefly. Be sure to check your particular implementation for the exact use of synonyms, if available. Note that MySQL does not support synonyms; you can implement the same type of functionality using a view instead.
Creating Synonyms
The general syntax for creating a synonym follows:
CREATE [PUBLIC|PRIVATE] SYNONYM SYNONYM_NAME FOR TABLE|VIEW
Next, take a look at an example of a synonym called MY_BIRDS for the BIG_BIRDS table. This is simply another name that can be used to access the same table or view.
CREATE SYNONYM MY_BIRDS FOR BIG_BIRDS;
Synonym created.
SQL> select bird_id, bird_name
2 from my_birds;
BIRD_ID BIRD_NAME
---------- ------------------------------
1 Great Blue Heron
3 Common Loon
4 Bald Eagle
5 Golden Eagle
7 Osprey
9 Canadian Goose
13 Ring-billed Gull
14 Double-crested Cormorant
16 Turkey Vulture
19 Mute Swan
20 Brown Pelican
21 Great Egret
12 rows selected.
Table owners also commonly create a synonym for the table to which you have been granted access so that you do not have to qualify the table name by the name of the owner:
CREATE SYNONYM BIRDS FOR RYAN.BIRDS; Synonym created.
Dropping Synonyms
Dropping synonyms works like dropping almost any other database object. The general syntax for dropping a synonym follows:
DROP [PUBLIC|PRIVATE] SYNONYM SYNONYM_NAME
Consider an example:
DROP SYNONYM MY_BIRDS; Synonym dropped.
Summary
This hour discussed two important features in SQL: views and synonyms. In many cases, these features can aid in the overall functionality of relational database users. Views are defined as virtual table objects that look and act like tables but do not take up physical space as tables do. 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. Various options are available 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 that another user owns and to which you have access. Two types of synonyms exist: PUBLIC and PRIVATE. A PUBLIC synonym 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 his or her own PRIVATE synonyms.
Q&A
Q. How can a view contain data but take up no storage space?
A. A view does not contain data; it 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 consists 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 give you the opportunity to apply the concepts discussed during the current hour, as well as build on the knowledge you acquired in previous hours of study. Be sure to complete the quiz questions and exercises before continuing to the next hour. 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. Do Oracle and SQL Server handle the capability to order a view in the same way?
5. Which option can you use when creating a view from a view to check integrity constraints?
6. 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 SQL statement to create a view based on the total contents of the
BIRDStable. Select all data from your view.2. Write a SQL statement that creates a summarized view containing the average wingspan of birds in each migration location. Select all data from your view.
3. Query your view to return only the migration locations that are above average in the average wingspan category.
4. Drop your view.
5. Create a view called
FISH_EATERSfor only those birds that eat fish. Select all data fromFISH_EATERS.6. Write a query joining your
FISH_EATERSview to theMIGRATIONtable, to return only migration locations for birds that eat fish.7. Create a synonym for your
FISH_EATERSview, and then write a query using the synonym.8. Experiment with some views of your own. Try joining views and tables, and employ some SQL functions that you previously learned.