8. Creating Views, Synonyms and Indexes

DBMaker provides several convenient methods of customizing and speeding up access to your data. Views and synonyms are supported to allow user-defined views and names for database objects. Indexes provide a much faster method of retrieving data from a table when you use a column with an index in a query.

8.1 Managing Views

DBMaker provides the ability to define a virtual table, called a view, which is based on existing tables and is stored in the database as a definition and a user-defined view name. The view definition is stored persistently in the database, but the actual data that you will see in the view is not physically stored anywhere. Rather, the data is stored in the base tables from which the view's rows are derived. A view is defined by a query which references one or more tables (or other views).

Views are a very helpful mechanism for using a database. For example, you can define complex queries once and use them repeatedly without having to re-invent them over and over. Furthermore, views can be used to enhance the security of your database by restricting access to a predetermined set of rows and/or columns of a table.

Since views are derived from querying tables, you can not determine the rows of the tables to update. Due to this limitation views can only be queried. Users can not update, insert into, or delete from views.

Creating Views

Each view is defined by a name together with a query that references tables or other views. You can specify a list of column names for the view different from those in the original table when creating a view. If you do not specify any new column names, the view will use the column names from the underlying tables.

For example, if you want users to see only three columns of the table Employees, you can create a view with the SQL command shown below. Users can then view only the FirstName, LastName and Telephone columns of the table Employees through the view empView.

dmSQL> create view empView (FirstName, LastName, Telephone) as 
              select FirstName, LastName, Phone from Employees;

The query that defines a view cannot contain the ORDER BY clause or UNION operator.

Dropping Views

You can drop a view when it is no longer required. When you drop a view, only the definition stored in system catalog is removed. There is no effect on the base tables that the view was derived from. To drop a view, execute the following command:

dmSQL> DROP VIEW empView;

8.2 Managing Synonyms

A synonym is an alias, or alternate name, for any table or view. Since a synonym is simply an alias, it requires no storage other than its definition in the system catalog.

Synonyms are useful for simplifying a fully qualified table or view name. DBMaker normally identifies tables and views with fully qualified names that are composites of the owner and object names. By using a synonym anyone can access a table or view through the corresponding synonym without having to use the fully qualified name. Because a synonym has no owner name, all synonyms in the database must be unique so DBMaker can identify them.

Creating Synonyms

You can create a synonym with the following SQL command:

dmSQL> create synonym Employees for Employees;

If the owner of the table Employees is the SYSADM, this command creates an alias named Employees for the table SYSADM.Employees. All database users can directly reference the table SYSADM.Employees through the synonym Employees.

Dropping Synonyms

You can drop a synonym that is no longer required. When you drop a synonym, only its definition is removed from the system catalog.

The following SQL command drops the synonym Employees:

dmSQL> drop synonym Employees;

8.3 Managing Indexes

An index provides support for fast random access to a row. You can build indexes on a table to speed up searching. For example, when you execute the query SELECT NAME FROM EMPLOYEES WHERE NUMBER = 10005, it is possible to retrieve the data in a much shorter time if there is an index created on the NUMBER column.

An index can be composed of more than one column, up to a maximum of 16 columns. Although a table can have up to 252 columns, only the first 127 columns can be used in an index.

An index can be unique or non-unique. In a unique index, no more than one row can have the same key value, with the exception that any number of rows may have NULL values. If you create a unique index on a non-empty table, DBMaker will check whether all existing keys are distinct or not. If there are duplicate keys, DBMaker will return an error message. After creating a unique index on a table, you can insert a row in this table and DBMaker will certify that there is no existing row that already has the same key as the new row.

When creating an index, you can specify the sort order of each index column as ascending or descending. For example, suppose there are five keys in a table with the values 1, 3, 9, 2, and 6. In ascending order the sequence of keys in the index is 1, 2, 3, 6, and 9, and in descending order the sequence of keys in the index is 9, 6, 3, 2, and 1.

When you implement a query, the index order will occasionally affect the order of the data output. For example, if you have a table name friends with NAME and AGE columns, the output will appear as below when you execute the query SELECT NAME, AGE FROM FRIEND_TABLE WHERE AGE > 20 using a descending index on the AGE column.

     name               age 
---------------- ----------------    
Jeff                           49 
Kevin                          40 
Jerry                          38 
Hughes                         30 
Cathy                          22

As for tables, when you create an index you can specify the fillfactor for it. The fill factor denotes how dense the keys will be in the index pages. The legal fill factor values are in the range from 1% to 100%, and the default is 100%. If you often update data after creating the index, you can set a loose fill factor in the index, for example 60%. If you never update the data in this table, you can leave the fill factor at the default value of 100%.

Before creating indexes on a table, it is recommended that you load all your data first, especially if you have a large amount of data for that table. If you create an index before loading the data into a table, the indexes will be updated each time you load a new row. As you can see, it is far more efficient to create an index after loading a large amount of data than to create an index before loading the data.

Creating Indexes

To create an index on a table, you must specify the index name and index columns. You can specify the sort order of each column as ascending (ASC) or descending (DESC). The default sort order is ascending.

For example, the following SQL command creates an index IDX1 on the column NUMBER of table EMPLOYEES in descending order.

dmSQL> create index idx1 on Employees (Number desc);

Also, if you want to create a unique index you have to explicitly specify it. Otherwise DBMaker implicitly creates non-unique indexes. The following example shows you how to create a unique index idx1 on the column Number of the table Employees:

dmSQL> create unique index idx1 on Employees (Number);

The next example shows you how to create an index with a specified fill factor:

dmSQL> create index idx2 on Employees(Number, LastName DESC) fillfactor 60;

Dropping Indexes

You can drop indexes using the DROP INDEX statement. In general, you might need to drop an index if it becomes fragmented, which reduces its efficiency. Rebuilding the index will create a denser, unfragmented index.

If the index is a primary key and is referred to by other tables, it cannot be dropped.

The following SQL command drops the index idx1 from the table Employees.

dmSQL> drop index idx1 from Employees;