New Document
Computer Science
Computer Catlog
Oracle Catlog

History of Oracle
Introduction to Terms
Oracle Configurations
Roles of Database Administrator
Oracle Architecture
A Brief History of SQL
Dr. Codd's 12 Rules
An Overview of SQL
The SELECT Statement
Expressions, Conditions, and Operators
Functions
Clauses in SQL
Joining Tables
Sub Query
Manipulating Data
Building a Database
Views and Indexes
Controlling Transactions
Database Security
Advanced SQL Topics
Cursors
Stored Procedures
Triggers
Embedded SQL
SQL Tuning
Using Views in Data Dictionary
Using SQL to Generate SQL Statements

Creating Views and Indexes


    This topic may be new even to programmers or database users. The common feature of the objects discussed so far--databases, tables, records, and fields--is that they are all physical objects located on a hard disk. This focus shifts to two features of SQL that enable you to view or present data in a different format than it appear on the disk. These two features are the view and the index. You will know the following:

How to distinguish between indexes and views

How to create views

How to create indexes

How to modify data using views

What indexes do


    A view is often referred to as a virtual table. Views are created by using the CREATE VIEW statement. After the view has been created, you can use the following SQL commands to refer to that view:

SELECT

INSERT

INPUT

UPDATE

DELETE

    An index is another way of presenting data differently than it appears on the disk. Special types of indexes reorder the record's physical location within a table. Indexes can be created on a column within a table or on a combination of columns within a table. When an index is used, the data is presented to the user in a sorted order, which you can control with the CREATE INDEX statement. You can usually gain substantial performance improvements by indexing on the correct fields, particularly fields that are being joined between tables.

NOTE:Views and indexes are two totally different objects, but they have one thing in common: They are both associated with a table in the database. Although each object's association with a table is unique, they both enhance a table, thus unveiling powerful features such as presorted data and predefined queries.


Using Views


    You can use views, or virtual tables, to encapsulate complex queries. After a view on a set of data has been created, you can treat that view as another table. However, special restrictions are placed on modifying the data within views. When data in a table changes, what you see when you query the view also changes. Views do not take up physical space in the database as tables do.The syntax for the CREATE VIEW statement is

SYNTAX:

CREATE VIEW <view-name> [(column1, column2...)] AS
SELECT <table-name column-names> FROM <table-name>

    As usual, this syntax may not be clear at first glance, but today's material contains many examples that illustrate the uses and advantages of views. This command tells SQL to create a view (with the name of your choice) that comprises columns (with the names of your choice if you like). An SQL SELECT statement determines the fields in these columns and their data types.

    Before you can do anything useful with views, you need to populate the BILLS database with a little more data.

    CREATE DATABASE, CREATE TABLE, and INSERT commands to input all this information, you are ready for an in-depth discussion of the view.

A Simple View


    Let's begin with the simplest of all views. Suppose, for some unknown reason, you want to make a view on the BILLS table that looks identical to the table but has a different name. (We call it DEBTS.) Here's the statement:

INPUT:

SQL> CREATE VIEW DEBTS AS SELECT * FROM BILLS;

    To confirm that this operation did what it should, you can treat the view just like a table:

    You can even create new views from existing views. Be careful when creating views of views. Although this practice is acceptable, it complicates maintenance. Suppose you have a view three levels down from a table, such as a view of a view of a view of a table. What do you think will happen if the first view on the table is dropped? The other two views will still exist, but they will be useless because they get part of their information from the first view. Remember, after the view has been created, it functions as a virtual table.

INPUT:

SQL> CREATE VIEW CREDITCARD-DEBTS AS SELECT * FROM DEBTS
WHERE ACCOUNT-ID = 4;

    The CREATE VIEW also enables you to select individual columns from a table and place them in a view. The following example selects the NAME and STATE fields from the COMPANY table.

INPUT:

SQL> CREATE VIEW COMPANY-INFO (NAME, STATE) AS SELECT * FROM COMPANY;

NOTE:Users may create views to query specific data. Say you have a table with 50 columns and hundreds of thousands of rows, but you need to see data in only 2 columns. You can create a view on these two columns, and then by querying from the view, you should see a remarkable difference in the amount of time it takes for your query results to be returned.


Renaming Columns


    Views simplify the representation of data. In addition to naming the view, the SQL syntax for the CREATE VIEW statement enables you to rename selected columns. Consider the preceding example a little more closely. What if you wanted to combine the ADDRESS, CITY, and STATE fields from the COMPANY table to print them on an envelope? The following example illustrates this. This example uses the SQL + operator to combine the address fields into one long address by combining spaces and commas with the character data.

INPUT:

SQL> CREATE VIEW ENVELOPE (COMPANY, MAILING-ADDRESS) AS
SELECT NAME, ADDRESS + " " + CITY + ", " + STATE FROM COMPANY;

ANALYSIS:

    The SQL syntax requires you to supply a virtual field name whenever the view's virtual field is created using a calculation or SQL function. This pro- cedure makes sense because you wouldn't want a view's column name to be COUNT (*) or AVG (PAYMENT).

NOTE:Check your implementation for the use of the + operator.


SQL View Processing


    Views can represent data within tables in a more convenient fashion than what actually exists in the database's table structure. Views can also be extremely convenient when performing several complex queries in a series (such as within a stored procedure or application program). To solidify your understanding of the view and the SELECT statement, the next section examines the way in which SQL processes a query against a view. Suppose you have a query that occurs often, for example, you routinely join the BILLS table with the BANK-ACCOUNTS table to retrieve information on your payments.

INPUT:

SQL> CREATE VIEW BILLS-DUE (NAME, AMOUNT, ACCT-BALANCE, BANK) AS
SELECT BILLS.NAME, BILLS.AMOUNT, BANK-ACCOUNTS.BALANCE, BANK-ACCOUNTS.BANK
FROM BILLS, BANK-ACCOUNTS
WHERE BILLS.ACCOUNT-ID = BANK-ACCOUNTS.ACCOUNT-ID;

    If you queried the BILLS-DUE view using some condition, the statement would look like this:

INPUT:

SQL> SELECT * FROM BILLS-DUE WHERE ACCT-BALANCE > 500;

ANALYSIS:

    SQL uses several steps to process the preceding statement. Because BILLS-DUE is a view, not an actual table, SQL first looks for a table named BILLS-DUE and finds nothing. The SQL processor will probably (depending on what database system you are using) find out from a system table that BILLS-DUE is a view. It will then use the view's plan to construct the following query:

SQL> SELECT BILLS.NAME, BILLS.AMOUNT, BANK-ACCOUNTS.BALANCE,
BANK-ACCOUNTS.BANK FROM BILLS, BANK-ACCOUNTS
WHERE BILLS.ACCOUNT-ID = BANK-ACCOUNTS.ACCOUNT-ID
AND BANK-ACCOUNTS.BALANCE > 500;

Restrictions on Using SELECT


    SQL places certain restrictions on using the SELECT statement to formulate a view. The following two rules apply when using the SELECT statement:

You cannot use the UNION operator.

You cannot use the ORDER BY clause. However, you can use the GROUP BY clause in a view to perform the same functions as the ORDER BY clause.

Modifying Data in a View


    As you have learned, by creating a view on one or more physical tables within a database, you can create a virtual table for use throughout an SQL script or a database application. After the view has been created using the CREATE VIEW...SELECT statement, you can update, insert, or delete view data using the UPDATE, INSERT, and DELETE commands.

INPUT:

SQL> CREATE VIEW LATE-PAYMENT AS SELECT * FROM BILLS;

SQL> UPDATE LATE-PAYMENT SET AMOUNT = AMOUNT * 1.10;

SQL> SELECT * FROM LATE-PAYMENT;

    To verify that the UPDATE actually updated the underlying table, BILLS, query the BILLS table:

    Now delete a row from the view:

INPUT:

SQL> DELETE FROM LATE-PAYMENT WHERE ACCOUNT-ID = 4;

SQL> SELECT * FROM LATE-PAYMENT

Problems with Modifying Data Using Views


    Because what you see through a view can be some set of a group of tables, modifying the data in the underlying tables is not always as straightforward. Following is a list of the most common restrictions you will encounter while working with views:

You cannot use DELETE statements on multiple table views.

You cannot use the INSERT statement unless all NOT NULL columns used in the underlying table are included in the view. This restriction applies because the SQL processor does not know which values to insert into the NOT NULL columns.

If you do insert or update records through a join view, all records that are updated must belong to the same physical table.

If you use the DISTINCT clause to create a view, you cannot update or insert records within that view.

You cannot update a virtual column (a column that is the result of an -expression or function).

Common Applications of Views


    Here are a few of the tasks that views can perform:

Providing user security functions

Converting between units

Creating a new virtual table format

Simplifying the construction of complex queries

Views and Security


    "Database Security," we briefly touch on the topic now to explain how you can use views in performing security functions. All relational database systems in use today include a full suite of built-in security features. Users of the database system are generally divided into groups based on their use of the database. Common group types are database administrators, database developers, data entry personnel, and public users. These groups of users have varying degrees of privileges when using the database. The database administrator will probably have complete control of the system, including UPDATE, INSERT, DELETE, and ALTER database privileges. The public group may be granted only SELECT privileges--and perhaps may be allowed to SELECT only from certain tables within certain databases.

    Views are commonly used in this situation to control the information that the database user has access to. For instance, if you wanted users to have access only to the NAME field of the BILLS table, you could simply create a view called BILLS-NAME:

    Someone with system administrator-level privileges could grant the public group SELECT privileges on the BILLS-NAME view. This group would not have any privileges on the underlying BILLS table. As you might guess, SQL has provided data security statements for your use also. Keep in mind that views are very useful for implementing database security.

Using Views to Convert Units


    Views are also useful in situations in which you need to present the user with data that is different from the data that actually exists within the database. For instance, if the AMOUNT field is actually stored in U.S. dollars and you don't want Canadian users to have to continually do mental calculations to see the AMOUNT total in Canadian dollars, you could create a simple view called CANADIAN-BILLS:

INPUT:

SQL> CREATE VIEW CANADIAN-BILLS (NAME, CAN-AMOUNT) AS
SELECT NAME, AMOUNT / 1.10 FROM BILLS;

SQL> SELECT * FROM CANADIAN-BILLS;

ANALYSIS:

    When converting units like this, keep in mind the possible problems inherent in modifying the underlying data in a table when a calculation (such as the preceding example) was used to create one of the columns of the view. As always, you should consult your database system's documentation to determine exactly how the system implements the CREATE VIEW command.

The DROP VIEW Statement


    In common with every other SQL CREATE... command; CREATE VIEW has a corresponding DROP... command. The syntax is as follows:

SYNTAX:

SQL> DROP VIEW view-name;

    The only thing to remember when using the DROP VIEW command is that all other views that reference that view are now invalid. Some database systems even drop all views that used the view you dropped. Using Personal Oracle7, if you drop the view BILLS1, the final query would produce the following error:

INPUT:

SQL> DROP VIEW BILLS1;

NOTE:A view can be dropped without any of the actual tables being modified, which explains why we often refer to views as virtual tables. (The same logic can be applied to the technology of virtual reality.)


Using Indexes


    Another way to present data in a different format than it physically exists on the disk is to use an index. In addition, indexes can also reorder the data stored on the disk (something views cannot do).

    Indexes are used in an SQL database for three primary reasons:

To enforce referential integrity constraints by using the UNIQUE keyword

To facilitate the ordering of data based on the contents of the index's field or fields

To optimize the execution speed of queries


What Are Indexes?


    Data can be retrieved from a database using two methods. The first method, often called the Sequential Access Method, requires SQL to go through each record looking for a match. This search method is inefficient, but it is the only way for SQL to locate the correct record. Think back to the days when libraries had massive card catalog filing systems. Suppose the librarian removed the alphabetical index cards, tossed the cards into the air, and then placed them back into the filing cabinets. When you wanted to look up this book's shelf location, you would probably start at the very beginning, then go through one card at a time until you found the information you wanted. (Chances are, you would stop searching as soon as you found any book on this topic!)

    Now suppose the librarian sorted the book titles alphabetically. You could quickly access this book's information by using your knowledge of the alphabet to move through the catalog.

    Imagine the flexibility if the librarian was diligent enough to not only sort the books by title but also create another catalog sorted by author's name and another sorted by topic. This process would provide you, the library user, with a great deal of flexibility in retrieving information. Also, you would be able to retrieve your information in a fraction of the time it originally would have taken.

    Adding indexes to your database enables SQL to use the Direct Access Method. SQL uses a treelike structure to store and retrieve the index's data. Pointers to a group of data are stored at the top of the tree. These groups are called nodes. Each node contains pointers to other nodes. The nodes pointing to the left contain values that are less than its parent node. The pointers to the right point to values greater than the parent node.

    The database system starts its search at the top node and simply follows the pointers until it is successful.

NOTE: The result of a query against the unindexed table is commonly referred to as a full-table scan. A full-table scan is the process used by the database server to search every row of a table until all rows are returned with the given condition(s). This operation is comparable to searching for a book in the library by starting at the first book on the first shelf and scanning every book until you find the one you want. On the other hand, to find the book quickly, you would probably look in the (computerized) card catalog. Similarly, an index enables the database server to point to specific rows of data quickly within a table.


    Fortunately, you are not required to actually implement the tree structure yourself, just as you are not required to write the implementation for saving and reading in tables or databases. The basic SQL syntax to create an index is as follows:

INPUT:

SQL> CREATE INDEX index-name ON table-name(column-name1, [column-name2], ...);

    As you have seen many times before, the syntax for CREATE INDEX can vary widely among database systems.

SYNTAX:

CREATE INDEX [schema.]index ON { [schema.]table (column [!!under!!ASC|DESC]
[, column [!!under!!ASC|DESC]] ...) | CLUSTER [schema.]cluster }
[INITRANS integer] [MAXTRANS integer] [TABLESPACE tablespace]
[STORAGE storage-clause] [PCTFREE integer] [NOSORT]

    SQL Server and Oracle allow you to create a clustered index. Oracle and Informix allow you to designate whether the column name should be sorted in ascending or descending order. We hate to sound like a broken record, but, once again, you should definitely consult your database management system's documentation when using the CREATE INDEX command.

    For instance, to create an index on the ACCOUNT-ID field of the BILLS table, the CREATE INDEX statement would look like this:

INPUT:

SQL> CREATE INDEX ID-INDEX ON BILLS( ACCOUNT-ID );

    The BILLS table is sorted by the ACCOUNT-ID field until the index is dropped using the DROP INDEX statement. As usual, the DROP INDEX statement is very straightforward:

SYNTAX:

SQL> DROP INDEX index-name;

ANALYSIS:

    Now the BILLS table is in its original form. Using the simplest form of the CREATE INDEX statement did not physically change the way the table was stored.

    You may be wondering why database systems even provide indexes if they also enable you to use the ORDER BY clause.

ANALYSIS:

    This SELECT statement and the ID-INDEX on the BILLS table generate the same result. The difference is that an ORDER BY clause re-sorts and orders the data each time you execute the corresponding SQL statement. When using an index, the database system creates a physical index object (using the tree structure explained earlier) and reuses the same index each time you query the table.

WARNING:When a table is dropped, all indexes associated with the table are dropped as well.


Indexing Tips


    Listed here are several tips to keep in mind when using indexes:

For small tables, using indexes does not result in any performance improvement.

Indexes produce the greatest improvement when the columns you have indexed on contain a wide variety of data or many NULL values.

Indexes can optimize your queries when those queries are returning a small amount of data (a good rule of thumb is less than 25 percent of the data). If you are returning more data most of the time, indexes simply add overhead.

Indexes can improve the speed of data retrieval. However, they slow data updates. Keep this in mind when doing many updates in a row with an index. For very large updates, you might consider dropping the index before you perform the update. When the update is complete, simply rebuild your index. On one particular update, we were able to save the programmers 18 hours by dropping the index and re-creating it after the data load.

Indexes take up space within your database. If you are using a database management system that enables you to manage the disk space taken up your database, factor in the size of indexes when planning your database's size.

Always index on fields that are used in joins between tables. This technique can greatly increase the speed of a join.

Most database systems do not allow you to create an index on a view. If your database system allows it, use the technique clause with the SELECT statement that builds the view to order the data within the view. (Unfortunately, many systems don't enable the ORDER BY clause with the CREATE VIEW statement either.)

Do not index on fields that are updated or modified regularly. The overhead required to constantly update the index will offset any performance gain you hope to acquire.

Do not store indexes and tables on the same physical drive. Separating these objects will eliminate drive contention and result in faster queries.

Indexing on More Than One Field


    SQL also enables you to index on more than one field. This type of index is a composite index. The following code illustrates a simple composite index. Note that even though two fields are being combined, only one physical index is created (called ID-CMPD-INDEX).

INPUT:

SQL> CREATE INDEX ID-CMPD-INDEX ON BILLS( ACCOUNT-ID, AMOUNT );

ANALYSIS:

    You can achieve performance gains by selecting the column with the most unique values. For instance, every value in the NAME field of the BILLS table is unique. When using a compound index, place the most selective field first in the column list. That is, place the field that you expect to select most often at the beginning of the list. (The order in which the column names appear in the CREATE INDEX statement does not have to be the same as their order within the table.) Assume you are routinely using a statement such as the following:

SQL> SELECT * FROM BILLS WHERE NAME = "Cable TV Company";

    To achieve performance gains, you must create an index using the NAME field as the leading column. Here are two examples:

SQL> CREATE INDEX NAME-INDEX ON BILLS(NAME, AMOUNT);
or

SQL> CREATE INDEX NAME-INDEX ON BILLS(NAME);

    The NAME field is the left-most column for both of these indexes, so the preceding query would be optimized to search on the NAME field.

    Composite indexes are also used to combine two or more columns that by themselves may have low selectivity.

NOTE:An index containing multiple columns is often referred to as a composite index. Performance issues may sway your decision on whether to use a single-column or composite index. In Oracle, for example, you may decide to use a single-column index if most of your queries involve one particular column as part of a condition; on the other hand, you would probably create a composite index if the columns in that index are often used together as conditions for a query. Check your specific implementation on guidance when creating multiple-column indexes.


Using the UNIQUE Keyword with CREATE INDEX


    Composite indexes are often used with the UNIQUE keyword to prevent multiple records from appearing with the same data. Suppose you wanted to force the BILLS table to have the following built-in "rule": Each bill paid to a company must come from a different bank account. You would create a UNIQUE index on the NAME and ACCOUNT-ID fields. Unfortunately, Oracle7 does not support the UNIQUE syntax. Instead, it implements the UNIQUE feature using the UNIQUE integrity constraint. The following example demonstrates the UNIQUE keyword with CREATE INDEX using Sybase's Transact-SQL language.

INPUT:

SQL> create unique index unique-id-name on BILLS(ACCOUNT-ID, NAME) go

SQL> select * from BILLS go

    Now try to insert a record into the BILLS table that duplicates data that already exists.

INPUT:

SQL> insert BILLS (NAME, AMOUNT, ACCOUNT-ID) values("Power Company", 125, 1) go

ANALYSIS:

    You should have received an error message telling you that the INSERT command was not allowed. This type of error message can be trapped within an application program, and a message could tell the user he or she inserted invalid data.

Indexes and Joins


    When using complicated joins in queries, your SELECT statement can take a long time. With large tables, this amount of time can approach several seconds (as compared to the milliseconds you are used to waiting). This type of performance in a client/server environment with many users becomes extremely frustrating to the users of your application. Creating an index on fields that are frequently used in joins can optimize the performance of your query considerably. However, if too many indexes are created, they can slow down the performance of your system, rather than speed it up. We recommend that you experiment with using indexes on several large tables (on the order of thousands of records). This type of experimentation leads to a better understanding of optimizing SQL statements.

NOTE:Most implementations have a mechanism for gathering the elapsed time of a query; Oracle refers to this feature as timing. Check your implementation for specific information.


    The following example creates an index on the ACCOUNT-ID fields in the BILLS and BANK-ACCOUNTS tables:

INPUT:

SQL> CREATE INDEX BILLS-INDEX ON BILLS(ACCOUNT-ID);

SQL> CREATE INDEX BILLS-INDEX2 ON BANK-ACCOUNTS(ACCOUNT-ID);

SQL> SELECT BILLS.NAME NAME, BILLS.AMOUNT AMOUNT,
BANK-ACCOUNTS.BALANCE ACCOUNT-BALANCE FROM BILLS, BANK-ACCOUNTS
WHERE BILLS.ACCOUNT-ID = BANK-ACCOUNTS.ACCOUNT-ID;

ANALYSIS:

    This example first created an index for the ACCOUNT-ID on both tables in the associated query. By creating indexes for ACCOUNT-ID on each table, the join can more quickly access specific rows of data. As a rule, you should index the column(s) of a table that are unique or that you plan to join tables with in queries.

Using Clusters


    Although we originally said that indexes can be used to present a view of a table that is different from the existing physical arrangement, this statement is not entirely accurate. A special type of index supported by many database systems allows the database manager or developer to cluster data. When a clustered index is used, the physical arrangement of the data within a table is modified. Using a clustered index usually results in faster data retrieval than using a traditional, nonclustered index. However, many database systems (such as Sybase SQL Server) allow only one clustered index per table. The field used to create the clustered index is usually the primary key field. Using Sybase Transact-SQL, you could create a clustered, unique index on the ACCOUNT-ID field of the BANK-ACCOUNTS table using the following syntax:

SYNTAX:

create unique clustered index id-index on BANK-ACCOUNTS(ACCOUNT-ID) go

    Oracle treats the concept of clusters differently. When using the Oracle relational database, a cluster is a database object like a database or table. A cluster is used to store tables with common fields so that their access speed is improved.

    Here is the syntax to create a cluster using Oracle7:

SYNTAX:

CREATE CLUSTER [schema.]cluster (column datatype [,column datatype] ... )
[PCTUSED integer] [PCTFREE integer] [SIZE integer [K|M] ] [INITRANS integer]
[MAXTRANS integer] [TABLESPACE tablespace] [STORAGE storage-clause]
[!!under!!INDEX | [HASH IS column] HASHKEYS integer]

    You should then create an index within the cluster based on the tables that will be added to it. Then you can add the tables. You should add tables only to clusters that are frequently joined. Do not add tables to clusters that are accessed individually through a simple SELECT statement.

    Obviously, clusters are a very vendor-specific feature of SQL. We will not go into more detail here on their use or on the syntax that creates them. However, consult your database vendor's documentation to determine whether your database management system supports these useful objects.

Summary


    Views are virtual tables. Views are simply a way of presenting data in a format that is different from the way it actually exists in the database. The syntax of the CREATE VIEW statement uses a standard SELECT statement to create the view (with some exceptions). You can treat a view as a regular table and perform inserts, updates, deletes, and selects on it. We briefly discussed the use of database security and how views are commonly used to implement this security. The basic syntax used to create a view is

CREATE VIEW view-name AS SELECT field-name(s) FROM table-name(s);

    Here are the most common uses of views:

To perform user security functions

To convert units

To create a new virtual table format

To simplify the construction of complex queries

    Indexes are also database design and SQL programming tools. Indexes are physical database objects stored by your database management system that can be used to retrieve data already sorted from the database. In addition, thanks to the way indexes are mapped out, using indexes and properly formed queries can yield significant performance improvements.

    The basic syntax used to create an index looks like this:

CREATE INDEX index-name ON table-name(field-name(s));

    Some database systems include very useful additional options such as the UNIQUE and CLUSTERED keywords.