A basic view is really a SQL statement stored in the database as an object. Conceptually, when you select from a view, Oracle looks up the view definition in the data dictionary, executes the query the view is based on, and returns the results.

In addition to selecting from a view, in some scenarios it is possible to execute INSERT, UPDATE, and DELETE statements against the view, which results in modifications to the underlying table data. So, in this sense, instead of simply describing a view as a stored SQL statement, it is more accurate to conceptualize a view as a logical table built on other tables or views, or both.

Here are some common uses for views:

•     Create an efficient method of storing a SQL query for reuse

•     Provide an interface layer between an application and physical tables

•     Hide the complexity of a SQL query from an application

•     Report to a user only a subset of columns or rows, or both

You should be able to start to see how useful views are for application APIs and how they can provide the needed data sets to applications and users.

Creating a View

You can create views on tables, materialized views, or other views. To create a view, your user account must have the CREATE VIEW system privilege. If you want to create a view in another user’s schema, then you must have the CREATE ANY VIEW privilege.

For reference, the view creation example in this section depends on the following base table:

SQL> create table sales ( sales_id number primary key , amnt number, state varchar2(2), sales_person_id number); Also assume that the table has the following data initially inserted into it:

SQL> insert into sales values(1, 222, ‘CO’, 8773), (20,827, ‘FL’, 9222); The CREATE VIEW statement is used to create a view.

The following code creates a view (or replaces it if the view already exists) that selects a subset of columns and rows from the SALES table:

SQL> create or replace view sales_rockies as select sales_id, amnt, state from sales where state in (‘CO’,’UT’,’WY’,’ID’,’AZ’); CREATE OR REPLACE VIEW is useful to modify a view if it exists or create a new view without having to verify if it already exists. If you don’t want to overwrite existing views, then use CREATE VIEW statements.

When you select from SALES_ROCKIES, it executes the view query and returns data from the SALES table as appropriate:

SQL> select * from sales_rockies; Given the view query, it is intuitive that the output shows only the following columns and one row:

SALES_ID    AMNT                    ST ———- ———- –1              222                    CO

What is not as apparent is that you can also issue UPDATE, INSERT, and DELETE statements against a view, which results in modification of the underlying table data. For example, the following insert statement against the view results in the insertion of a record in the SALES table:

SQL> insert into sales_rockies ( sales_id, amnt, state) Values (2, 100, ‘CO’);

Additionally, as the owner of the table and view (or as a DBA), you can grant DML privileges to other users on the view.

For instance, you can grant SELECT, INSERT, UPDATE, and DELETE privileges on the view to another user, which will allow the user to select and modify data referencing the view to another user, which will allow the user to select and modify data referencing the view. However, having privileges on the view does not give the user direct SQL access to the underlying tables.

Any users granted privileges on the view will be able to manipulate data through the view but not issue SQL against the object the view is based on.

If you create the view using the WITH READ ONLY clause, users cannot perform INSERT, UPDATE, or DELETE operations on the view.

This is useful if you use views for reporting and never intend for the view to be used as a mechanism for modifying the underlying table’s data; then you should always create the views with the WITH READ ONLY clause. Doing so prevents accidental modifications to the underlying tables through a view that was never intended to be used to modify data.

If you have identified an index that is no longer being used, you can mark it UNUSABLE. From that point forward, Oracle will not maintain the index, nor will the optimizer consider the index for use in SELECT statements.

The advantage of marking the index UNUSABLE (rather than dropping it) is that if you later determine that the index is being used, you can alter it to a USABLE state and rebuild it without needing the DDL on hand to re-create it.

Here is an example of marking an index UNUSABLE:

SQL> alter index cust_idx1 unusable;

You can verify that it is unusable via this query:

SQL> select index_name, status from user_indexes;

The index has an UNUSABLE status:

INDEX_NAME                  STATUS

——————– ——–CUST_IDX1                             UNUSABLE

If you determine that the index is needed (before you drop it), then it must be rebuilt to become usable again:

SQL> alter index cust_idx1 rebuild;

Another common scenario for marking indexes UNUSABLE is that you are performing a large data load. When you want to maximize table-loading performance, you can mark the indexes UNUSABLE before performing the load. After you have loaded the table, you must rebuild the indexes to make them usable again.

Note the alternative to setting an index to UNUSABLE is to drop and re-create it. this approach requires the CREATE INDEXddL.

Dropping an Index

If you have determined that an index is not being used, then it is a good idea to drop it. Unused indexes take up space and can potentially slow down DML statements (because the index must be maintained as part of those DML operations). You can always test the performance by making an index invisible first before dropping. Remember there is time and resources involved in creating a large index on a widely used table, so the validation is a good set up before hours of poor performance while you rebuild an index. Use the DROP INDEX statement to drop an index:

SQL> drop index cust_idx1;

Dropping an index is a permanent DDL operation; there is no way to undo an index drop other than to re-create the index. Before you drop an index, it does not hurt to quickly capture the DDL required to re-create the index. Doing so will allow you to re-create the index in the event you subsequently discover that you did need it after all.

Indexing Foreign Key Columns

Foreign key constraints ensure that when inserting into a child table, a corresponding parent table record exists. This is the mechanism for guaranteeing that data conforms to parent–child business relationship rules. Foreign keys are also known as referential integrity constraints.

Unlike primary key and unique key constraints, Oracle does not automatically create indexes on foreign key columns. Therefore, you must create a foreign key index manually, based on the columns defined as the foreign key constraint. In most scenarios, you should create indexes on columns associated with a foreign key. Here are two good reasons:

•     Oracle can often make use of an index on foreign key columns to improve the performance of queries that join a parent table and child table (using the foreign key columns).

•     If no B-tree index exists on the foreign key columns, when you insert or delete a record from a child table, all rows in the parent table are locked for the duration of the statement, for the update or delete. For applications that actively modify both the parent and child tables, this can cause locking and deadlock issues, but for the duration of the statement.

One could argue that if you know your application well enough and can predict that queries will not be issued and that join tables on foreign key columns and that certain update/delete scenarios will never be encountered (that result in entire tables being locked), then, by all means, do not place an index on foreign key columns.

In my experience, however, this is seldom the case: developers rarely think about how the “black-box database” might lock tables; some DBAs are equally unaware of common causes of locking; teams experience high turnover rates, and the DBA de jour is left holding the bag for issues of poor database performance and hung sessions.

Considering the time and resources spent chasing down locking and performance issues, it does not cost that much to put an index on each foreign key column in your application.

I know some purists will argue against this, but I tend to avoid pain, and an unindexed foreign key column is a ticking bomb.

Having made my recommendation, I’ll first cover creating a B-tree index on a foreign key column. Then, I’ll show you some techniques for detecting unindexed foreign key columns.

As applications age, you invariably have to perform some maintenance activities on existing indexes. You may need to rename an index to conform to newly implemented standards, or you may need to rebuild a large index to move it to a different tablespace that better suits the index’s storage requirements. The following list shows common tasks associated with index maintenance:

•     Renaming an index

•     Displaying the DDL for an index

•     Rebuilding an index

•     Setting indexes to unusable

•     Monitoring an index

•     Dropping an index

Each of these items is discussed in the following sections.

Renaming an Index

Sometimes you need to rename an index. The index may have been erroneously named when it was created, or perhaps you want a name that better conforms to naming standards. Use the ALTER INDEX … RENAME TO statement to rename an index:

SQL> alter index cust_idx1 rename to cust_index1;

You can verify that the index was renamed by querying the data dictionary:

SQL> select table_name ,index_name ,index_type ,tablespace_name ,status from user_indexes order by table_name, index_name;

Displaying Code to Re-create an Index

You may be performing routine maintenance activities, such as moving an index to a different tablespace, and before you do so, you want to verify the current storage settings. You can use the DBMS_METADATA package to display the DDL required to re-create an index. Here is an example:

SQL> set long 10000

SQL> select dbms_metadata.get_ddl(‘INDEX’,’CUST_IDX1′) from dual;

Here is a partial listing of the output:

SQL> CREATE INDEX “MV_MAINT”.”CUST_IDX1″ ON “MV_MAINT”.”CUST” (“CUST_ID”) PCTFREE 10 INITRANS 2 MAXTRANS 255 INVISIBLE COMPUTE STATISTICS

To show all index DDL for a user, run this query:

SQL> select dbms_metadata.get_ddl(‘INDEX’,index_name) from user_indexes;

You can also display the DDL for a particular user. You must provide as input to the GET_DDL function the object type, object name, and schema; for example,

SQL> select

dbms_metadata.get_ddl(object_type=>’INDEX’, name=>’CUST_IDX1′, schema=>’INV’)

from dual;

Rebuilding an Index

There are a couple of good reasons to rebuild an index:

•     Modifying storage characteristics, such as changing the tablespace

•     Rebuilding an index that was previously marked unusable to make it usable again

Use the REBUILD clause to rebuild an index. This example rebuilds an index named CUST_IDX1:

SQL> alter index cust_idx1 rebuild;

Oracle attempts to acquire a lock on the table and rebuild the index. If there are any active transactions that haven’t committed, Oracle will not be able to obtain a lock, and the following error will be thrown:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

In this scenario, you can either wait until there is little activity in the database or try setting the DDL_LOCK_TIMEOUT parameter:

SQL> alter session set ddl_lock_timeout=15;

The DDL_LOCK_TIMEOUT initialization parameter instructs Oracle to repeatedly attempt to obtain a lock (for 15 seconds, in this case).

Transactions can block rebuilding indexes, but the index rebuild itself can block other transactions until the rebuild is complete. To avoid a rebuild from blocking transactions, use the key ONLINE:

SQL> alter index cust_idx1 rebuild online;

If no tablespace is specified, Oracle rebuilds the index in the tablespace in which the index currently exists. Specify a tablespace if you want the index rebuilt in a different tablespace:

SQL> alter index cust_idx1 rebuild tablespace reporting_index;

If you are working with a large index, you may want to consider using features such as NOLOGGING or PARALLEL, or both. This next example rebuilds an index in parallel, while generating a minimal amount of redo:

SQL> alter index cust_idx1 rebuild parallel nologging;