Views

Indexes

Synonyms

 

What is a view?

"A view is a database object that allows you to create a customized slide of a table or a collection of tables. A view is just a SQL query that is stored in the database. The results of that query are returned in the form a table." [1] "A view can be created by selecting columns from a larger table, or a view can be written to restrict the number of rows or type of data returned from a select. You can define a view that joins several tables together." [2] A view extracts its data from one or more underlying actual tables. The underlying tables are known as base tables. Views can be based on tables or other views.

Any changes you make to the base tables of a view directly change the view. If you delete a table that a view is based on, then the view is invalid. The view is valid only if the base tables on which the view is built exist.

In order to create a view:
  1. The user must have the create view privilege to create a view in his own schema. The user must have the create any view privilege to create a view in another user's schema.
  2. "The username you use to create the view must have access to all of the base tables referenced within the view." [3]

There are 2 ways to create a view: through SQL*Plus or the Navigator

SQL*Plus

An example of creating a view using SQL*Plus:
  1. Start SQL*Plus:
    click on Start|Programs|Oracle for Windows 95|SQL Plus 8.0.
  2. In the Log On screen, type SAM as the user name and SAM as the password. Then click okay.
  3. Use the create view command to create a view called mostgoals that is a subset of the PLAYER table that only consists of the players who hit more than 3 goals.


    create view mostgoals as
    select player_no, name
    from player
    where goals_hit > 3;
  4. To check the view, use the command:
select * from mostgoals;

To create a join view, which is a view made up from more than one table:
  1. Create a view called assign_view that is a subset of the
    2. PLAYER, ASSIGNMENT, and EQUIP tables that displayed the equipment used by each player.

    create view assign_view as
    select p.player_no, p.name, e.equip_name
    from player p,
    assignment a,
    equip e
    where p.player_no = a.player_no
    and a.equip_id = e.equip_id;
  2. To check the view, use the command:
    3.

select * from assign_view;

To drop a view:

The Navigator

An example of creating a view using the Navigator:
  1. Open the Navigator using the following path: Start|Programs| Oracle8 Personal Edition Oracle8 Navigator.
  2. Click on the '+' sign to the left of the Oracle8 Personal Edition folder.
  3. Click on the '+' sign to the left of the Projects folder.
  4. Right click on the Soccer folder. Click New and then View. This will open the New View Properties window.
  5. Type sam_view in the name field.
  6. Type SAM in the owner field.
  7. Click on the Design tab.
  8. Choose the default (Oracle8 Personal Edition) in the Database field.
  9. After you have specified which database to use, you will be prompted for the password of the owner.
  10. You will be shown the tables and views you have access to.
  11. Expand the Table folder, by clicking on the ‘+’
  12. Double click on the SAM icon.
  13. Double click on the PLAYER table to show you the columns contained within it.
  14. Double click on the Name and Date_Begin columns. These columns will now appear in the lower section of this window. These columns will make up the view.
  15. Click OK.
Back to Top

What is an index?

An index is built of columns in a table that are queried often. An index is used to speed up access to rows within a table. "Without an index, each time you wanted to retrieve information, Oracle would have to perform a full table scan of the data and look at each and every row in each table of interest. If you have a commonly used query that just retrieves two columns of a table containing a large volume of information, you can create an index on those two columns. Oracle will look in the index for the information you want instead of walking through every complete row in the table." [2]

To create a simple index:

 An example
  1. Start SQL*Plus:
    click on Start|Programs|Oracle for Windows 95|SQL Plus 8.0.
  2. In the Log On screen, type SAM as the user name and SAM as the password. Then click okay.
  3. Create an index for the Begin_Date column in the PLAYER table.

create index bdate on player (begin_date);

The following command:

select name from player

where begin_date > ‘15-JAN-98’;

Would cause the system to search the index bdate to find players who started the team after January 15, 1998. In tables that consist of many columns and thousands of rows, using indexes will on average speed up access time.

To drop an index:
  • Use the drop index command.
  • The syntax:

    drop index index_name;
Back to Top

What is a synonym?

"Synonyms are often referred to as nicknames or shortcuts or alternatives for the syntax object_owner.object_name." Synonyms can be created for tables, views, procedures, packages, or sequences. [2]

Why are synonyms used?

Synonyms are most frequently used for security. By using synonyms, you can hide the location of the object and the object owner. For example, in the Soccer database, the table PLAYER is owned by the schema SAM. Each time you refer to the PLAYER table, you have to use the syntax SAM.PLAYER so it will be clear exactly which table you are referencing. But if you create a synonym for the PLAYER table, called PLAY, you can reference the table by the synonym name, without using the owner of the table (SAM).

Synonyms also can be used to simplify SQL statements. For example, if you have a table name that is very long, you can shorten it by using a synonym and reference the synonym in the SQL statement.

There are two types of synonyms: public and private.

Public Synonyms

Public synonyms are owned by the PUBLIC user. Therefore, public synonyms are accessible to all users within the database. You must have CREATE PUBLIC SYNONYM privilege to create a public synonym.

Private Synonyms

Private synonyms are "stored in the user’s schema who creates the private synonym." [3] You must have the CREATE SYNONYM privilege to create a private synonym. You must have the CREATE ANY SYNONYM privilege to create a private synonym in another user’s schema.

Example of creating a private synonym:

The Soccer database is created by user SAM. All tables are stored in the SAM schema. Suppose user SALLY needs access to the EQUIP table in the SAM schema.

  1. Start SQL*Plus:
    click on Start|Programs|Oracle for Windows 95|SQL Plus 8.0.
  2. In the Log On screen, type SAM as the user name and SAM as the password. Then click okay.
  3. Grant SALLY the sam_role, which consists of the create any synonym, insert any table, select any table, and update any table privileges (the sam_role was created in the previous module using the Oracle8 Navigator). SALLY will need these privileges in order to have access to the tables in the SAM schema and to be able create a private synonym.
  4. Use the connect command to log on as SALLY (password: GUEST)

    connect sally/guest;

  5. Create a private synonym for the EQUIP table, as shown below.

    SQL>create synonym eq for sam.equip;
    Synonym created.

  6. Use the private synonym, EQ, to select all columns from the EQUIP table.

SQL>select * from eq;

EQUIPMENT_ID EQIUPMENT_NAME NO_IN_STOCK
1001 Ball 25
1002 Jersey 20
1003 ShinGuard 25
1004 Cone 25

Suppose other users need access to the EQUIP table. You cannot expect each user to create his/her own synonym. This is when you, the DBA, could create a public synonym, which would make the EQUIP table accessible to everyone. However, each user would "still need to be granted access to the table for any data to be seen." [3] It is best for a dba user, such as SYSTEM, to create a public synonym.

Example of creating a public synonym:
  1. Start SQL*Plus:
    click on Start|Programs|Oracle for Windows 95|SQL Plus 8.0.
  2. In the Log On screen, type SYSTEM as the user name and MANAGER as the password. Then click okay.
  3. Create a public synonym for the EQUIP table, as shown below

    4. SQL> create public synonym pubeq for sam.equip;
    Synonym created.
  4. Test the public synonym, pubeq. Use the connect command to log on to the database as the user TOM, whose username and password are the same. Select all rows from the EQUIP table by using the public synonym, pubeq.
SQL> connect tom/tom; Connected.
SQL>select * from pubeq;

EQUIPMENT_ID EQIUPMENT_NAME NO_IN_STOCK
1001 Ball 25
1002 Jersey 20
1003 ShinGuard 25
1004 Cone 25

Now TOM can see the data in the EQUIP table. By using synonyms, TOM does not know who owns the EQUIP table or where the table is stored in the database.

Dropping a private synonym
 To drop a private synonym that is in your schema you must have the drop any synonym privilege.

An example:

  1. Start SQL*Plus:
    click on Start|Programs|Oracle for Windows 95|SQL Plus 8.0.
  2. In the Log On screen, type SALLY as the user name and GUEST as the password. Then click okay.

    SQL> connect sally/guest;
  3. Drop the private synonym eq as shown below.
SQL> drop synonym eq;
Synonym dropped.

"Notice that you do not have to specify that this is a private synonym if you use DROP SYNONYM. Oracle automatically knows this is a private synonym." [3]

Dropping a public synonym
 To drop a public synonym you must have the drop public synonym privilege.

An example:

  1. Start SQL*Plus:
    click on Start|Programs|Oracle for Windows 95|SQL Plus 8.0.
  2. In the Log On screen, type SYSTEM as the user name and MANAGER as the password. Then click okay.

    SQL> connect system/manager;
  3. Drop the public synonym pubeq as shown below.
SQL> drop public synonym pubeq;
Synonym dropped.

"Notice that the word PUBLIC is specified. If not, Oracle would try to drop a private synonym by the same name." [3]

 Back to Top
Return To Main