When an update or delete is issued against a row that participates in the index, the database will attempt an exclusive lock on the row, which requires the task to check if any shared locks are held against the row as well as any index nodes that will be affected. Many indexing algorithms allow for the index tree to dynamically change shape, spawning new levels as items are added and condensing levels as items are deleted.

However, for any table of consequential size, indexes are recommended to improve performance. Of course, indexes require additional disk space, and a table with an index on each column will have indexes that consume more space than the table that they support. Oracle will also update the indexes at runtime as columns are deleted, added, or modified—and this index updating can cause considerable performance degradation. For example, adding a row to the end of a table will cause Oracle to adjust the high-key value for each node in the table.

One guideline for determining when to use an index involves examination of the SQL that is issued against the tables. In general, the SQL can be collected, and each value supplied in each SQL WHERE clause could be a candidate for inclusion in an index.

Another common approach for determining where to create indexes is to run an explain plan for all SQL and carefully look for any full-table scans. The Oracle optimizers operate in such a fashion that Oracle will sometimes perform a full-table scan, even if an index has been defined for the table. This occurs most commonly when issuing complex n-way joins, and techniques for avoiding this problem are discussed in Chapter 4, Tuning Oracle SQL. If you are using rule-based optimization in Oracle, the structure of the SQL statement can be adjusted to force the use of an existing index. For Oracle’s cost-based optimizer, adding “hints” to the structure will ensure that all indexes are used.

However, indexes do much more than speed up an individual query. When full-table scans are performed on a large Oracle table, the buffer pool begins to page out blocks from other queries. This causes additional I/O for the entire database and results in poor performance for all queries—not just the offending full-table scan.

Indexes are never a good idea for long descriptive columns. A column called customer_description would be a poor choice for an index because of its length and the inconsistency of the data within the column. If this column was 300 bytes, Oracle would be forced to do a huge amount of processing whenever the column is altered. Also a field such as customer_description would usually be referenced in SQL by using Oracle extensions such as substr, like, and upper. Remember, these Oracle extensions invalidate the index. Suppose that an index has been created on customer_last_name. The following query would use the index:

SELECT STATUS
FROM CUSTOMER
WHERE
customer_last_name = 'BURLESON';

The following queries would bypass the index, causing a full-table scan:

SELECT STATUS
FROM CUSTOMER
WHERE
customer_last_name = lower('burleson');

SELECT STATUS
FROM CUSTOMER
WHERE
customer_last_name like 'BURL%';

Unlike other relational databases such as DB2, we cannot physically load a table in key order. Consequently, we can never guarantee that the rows in the table will be in any particular order.

The use of an index can also help whenever the SQL ORDER BY clause is used. For example, even if there are no complex WHERE conditions, the presence of a WHERE clause will assist the performance of the query. Consider the following SQL:

SELECT customer_last_name, customer_first_name
FROM CUSTOMER
ORDER BY customer_last_name, customer_first_name;

Here, building a multi-valued index on customer_last_name and customer_first_name will alleviate the need for an internal sort of the data, significantly improving the performance of the query:

CREATE INDEX  cust_name
ON CUSTOMER
(customer_last_name, customer_first_name) ascending;

Constraints And Indexes

In Oracle, some constraints will create an index on your behalf. For example, creating a primary key constraint on the customer table for the cust_id will create an index on the field, and it is not necessary to manually build an index (see Listing 2.1).

Listing 2.1 Creating a primary key constraint.

CREATE TABLE CUSTOMER (
        cust_nbr               number
        CONSTRAINT cust_ukey
PRIMARY KEY (CUST_NBR)
USING INDEX
PCTFREE  10
INITRANS 2
MAXTRANS 255
TABLESPACE TS1
STORAGE  (
  INITIAL   256000
  NEXT   102400
  MINEXTENTS  1
  MAXEXTENTS  121
  PCTINCREASE  1 ),
        dept_name              char(10)
        CONSTRAINT dept_fk references DEPT ON DELETE CASCADE,

        organization_name      char(20)
        CONSTRAINT org_fk references ORG ON DELETE RESTRICT,

        region_name            char(2)
        CONSTRAINT state_check
        CHECK region_name in ('NORTH', SOUTH', 'EAST', 'WEST')
);

Note that you should always specify the location clause when declaring constraints. In the previous example, had the cust_ukey constraint been defined without the STORAGE clause, the index would have been placed in whatever tablespace is specified by the table owner’s DEFAULT tablespace, with whatever default storage parameters are in effect for that tablespace.

Listing 2.1 shows us some examples of Oracle constraints. The first constraint is on the cust_nbr column, the primary key. When we use Oracle’s RI to specify a primary key, Oracle automatically builds a unique index on the column to ensure that no duplicate values are entered.

The second constraint is on the dept_name column of the DEPT table. This constraint tells Oracle that it may not remove a department row if there are existing customer rows that reference that department. The ON DELETE CASCADE tells Oracle that when the department row is deleted, all customer rows that reference that department will also be deleted.

The next RI constraint on organization_name ensures that no organization is deleted if customers are participating in that organization. ON DELETE RESTRICT tells Oracle not to delete an organization row if any customer rows still reference the organization. Only after each and every customer has been set to another organization can the row be deleted from the organization table.

The last RI constraint is called a “check” constraint: The Oracle will verify that the column is one of the valid values before inserting the row, but it will not create an index on the column.