Previous | Table of Contents | Next

Page 132

Listing 6.16 L_06_16.sql—Creating 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.

Creating and Dropping Views

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.sql—Creating 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.sql—Creating 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.sql—Using 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.

Creating Synonyms

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.TXT—Creating 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.

Previous | Table of Contents | Next