Page 132
Listing 6.16 L_06_16.sqlCreating a Unique Index
SQL> create unique index deptind2 on department 2 (department_name); Index created. SQL> drop index deptind2; Index dropped.
Remember these rules about indexes: You may index up to 16 columns of a table in a single index; and the name of the index must be unique and follow the same naming convention as used for tables.
Views are predefined queries that produce virtual tables or sets of data when executed. The view name is used in a select statement in place of a table. When Oracle encounters the view in the from clause, it executes the query to produce the virtual table. You can use views to simplify the acquisition of data for the users, to limit access to data, and to compile information that cannot be developed without the use of views. Listing 6.17 illustrates the creation of a view that compiles the total cost of each employee's tool purchases.
To define a view, use the create view command. The view name, the keyword as, and the select clause that will be used to produce the data follow the keywords. You do not use the order by clause in the view's select statement.
Listing 6.17 illustrates the creation of a view that joins the employee table and the tools table. The fourth expression `sum(cost)' will have a virtual column name of `cost.' You use this name when the view is used as a table in a Select statement.
Listing 6.17 L_06_17.sqlCreating a View Called Toolcost
SQL> create view toolcost as 2 select payroll_number, last_name, 3 first_name, fk_department, sum(tool_cost) cost 4 from employee, tools 5 where payroll_number = fk_payroll_number(+) 6 group by payroll_number, last_name, first_name, 7 fk_department; View created.
Listing 6.17 is an example of a view that simplifies the acquisition of data for the user. A user of this view would not have to worry about the joining of the two tables, the sum function, or the group by clause. A simple select statement using the view name in the from clause produces the results. Finally, this view accesses four columns from the two tables. Granting users access to this view, but not to the employee or department tables, effectively limits their access to data.
Page 133
Listing 6.18 creates a view that sums the cost of each department's tools. The toolcost view and the deptcost view in Listing 6.19 will produce a virtual table of the employees, the cost of the employee's tools, the total tool cost of the employee's department, and the percentage that the tool cost is of the department's total. This query would be difficult or impossible to produce without the use of views. The reason for the difficulty is that each of the views compute different summary totals. To put these totals on the same row and use them in an arithmetic expression, you need to create the views and join the results.
Listing 6.18 L_06_18.sqlCreating a View Called Deptcost
SQL> create view deptcost as 2 select department, sum(tool_cost) deptcost 3 from department, employee, tools 4 where payroll_number = fk_payroll_number(+) 5 and department = fk_department 6 group by department; View created.
Listing 6.19 L_06_19.sqlUsing the Toolcost and Deptcost View to
Produce a Listing
SQL> select department, last_name, first_name, 2 cost, deptcost, (cost/deptcost)*100 percent 3 from toolcost, deptcost 4 where department = fk_department; DEPA LAST_NAME FIRST_NAME COST DEPTCOST PERCENT ---- --------------- --------------- --------- --------- --------- INT ROOSEVELT THEODORE 324 792.2 40.898763 INT COOLIDGE CALVIN 35 792.2 4.4180762 INT TRUMAN HAROLD 792.2 INT EISENHOWER DWIGHT 375 792.2 47.336531 INT FORD GERALD 12 792.2 1.514769 INT BUSH GEORGE 46.2 792.2 5.8318606 POL JOHNSON ANDREW 16.7 172.15 9.7008423 POL ROOSEVELT FRANKLIN 20 172.15 11.617775 POL KENNEDY JOHN 172.15 POL NIXON RICHARD 18.5 172.15 10.746442 POL CLINTON WILLIAM 172.15 POL JOHNSON LYNDON 172.15 POL WILSON WOODROW 116.95 172.15 67.93494 WEL ROOSEVELT ELEANOR 61.95 226.5 27.350993 WEL CARTER JIMMY 226.5 WEL REAGAN RONALD 28.7 226.5 12.671082 WEL TAFT WILLIAM 23 226.5 10.154525 WEL HOOVER HERBERT 24 226.5 10.596026 WEL ANTHONY SUSAN 88.85 226.5 39.227373
19 rows selected.
Page 134
You cannot alter views; you can, however, drop them. To accomplish this, use the Drop View command followed by the view name. Views are data definitions and do not have any effect on performance because they are only activated when used in a select statement. This means that there are no performance reasons for them to be eliminated. You can use the select name from Tab command to list the views that you have created.
A synonym is another name for a table or view. You can use it to make a database more user friendly. At the company that I work for, the data administrators and database administrators believe in making the name of tables descriptive. I use a table called the `T_and_D_Cable_Terminal_Poles,' for example. This name is very cumbersome and time consuming to use. I had the database administrator create a synonym for this table called `CTPS.' This made the use of the table much more user friendly because the slang for the items in the table is `CTPS,' and I only had to type 4 characters rather than 28 when I used the table. The syntax of the command is create [public] synonym [user] synonym name for object name.
Listing 6.20 illustrates the syntax of the command to create a synonym. The word public makes the synonym available to all users. When the table or view exists on another Oracle id, the `user.' qualifier needs to be used. This tells Oracle where the object exists. Synonyms can also tell Oracle on which server the table is located. This is done by incorporating the datalink into the synonym. The following section discusses the database link further. You use the link in a Select statement following the table name. To avoid the cumbersome process of putting a database link in the from clause, you can create synonyms that have the database link incorporated. This means that the user does not have to worry about incorporating the link in his Select statements. To delete the synonym, use the Drop Synonym command. Only DBAs can drop public synonyms or the synonyms of other users.
Listing 6.20 L_06_20.TXTCreating a Public Synonym Called CTPS
SQL> create table t_and_d_cable_terminal_poles (ctpnum number); Table created. SQL> create public synonym ctps for t_and_d_cable_terminal_poles; Synonym created. SQL> drop public synonym ctps; Synonym dropped. SQL> drop table t_and_d_cable_terminal_poles; Table dropped.