Pro Oracle Database 11g Administration (34 page)

Oracle also provides various constraints to help you manage the data within tables. Constraints form the bedrock of data integrity. In most cases, each table should include a primary-key constraint that ensures that every row is uniquely identifiable. Additionally, any parent/child relationships should be enforced with foreign-key constraints. You can use unique constraints to implement business rules that require a column or combination of columns to be unique. Check and NOT NULL constraints ensure that columns contain business-specified data requirements.

After you create tables, the next logical activity is to create indexes where appropriate. Indexes are optional database objects that help improve performance. Index-creation and -maintenance tasks are covered in the next chapter.

170

CHAPTER 8 ■ INDEXES

must be sure that the statements that are improved warrant the penalty being applied to other statements.

You should only add an index when you’re certain it will improve performance. And after an index is implemented, you should monitor it to ensure that the Oracle optimizer is using it to speed up queries.

What to Think About

Oracle provides a wide assortment of indexing features and options. As a DBA or a developer, you need to be aware of the various features and how to utilize them. If you choose the wrong type of index or use a feature incorrectly, there may be serious detrimental performance implications. Listed next are aspects to consider before you create an index:

• Type of index

• Table column(s) to include

• Whether to use a single column or a combination of columns

• Special features such as the PARALLEL clause, NOLOGGING, compression, invisible indexes, and so on

• Uniqueness

• Naming conventions

• Tablespace placement

• Initial sizing requirements and growth

• Impact on performance of SELECT statements (improvement)

• Impact on performance of INSERT, UPDATE, and DELETE statements

• Global or local index, if the underlying table is partitioned

When you create an index you should give some thought to every aspect mentioned in the previous list. One of the first decisions you need to make is the type of index and the columns to include. Oracle provides a robust variety of index types. For most scenarios, you can use the default B-tree (balanced tree) index type. Other commonly used types are bitmap and function-based indexes. Table 8–1

describes the types of indexes available with Oracle.

172

CHAPTER 8 ■ INDEXES

Table 8–1.
Oracle Index Type Descriptions

Index Type

Usage

B-tree

Default, balanced tree index, good for high-

cardinality columns.

B-tree cluster

Used with clustered tables.

Hash cluster

Used with hash clusters.

Function

Good for columns that have SQL functions applied

to them.

Global partitioned

Global index across all partitions in a partitioned

table.

Local partitioned

Local index based on individual partitions in a

partitioned table.

Reverse key

Useful to balance I/O in an index that has many

sequential inserts.

Key compressed

Useful for concatenated indexes where the leading

column is often repeated. Compresses leaf block

entries.

Bitmap

Useful in data-warehouse environments with low-

cardinality columns. These indexes aren’t

appropriate for online transaction processing

(OLTP) databases where rows are heavily updated.

Bitmap join

Useful in data-warehouse environments for

queries that join fact and dimension tables.

Domain

Specific for an application or cartridge.

This chapter focuses on the most commonly used indexes and features: B-tree, bitmap, reverse-key, and function-based indexes, and useful options. Partitioned indexes are covered in Chapter 12. If you need more information about index types or features not covered in this chapter or book, see Oracle’s SQL Reference guide at http://otn.oracle.com.

Index-Management Guidelines

Misusing indexes can have serious negative performance effects. Indexes created of the wrong type or on the wrong columns do nothing but consume space and processing resources. As a DBA, you must have a strategy to ensure that indexes enhance performance and don’t negatively impact applications.

173

CHAPTER 8 ■ INDEXES

Table 8–2 encapsulates many of the index-management concepts covered in this chapter. These recommendations aren’t written in stone: adapt and modify them as needed for your environment.

Table 8–2.
Index-Creation and -Maintenance Guidelines

Guideline Reasoning

Add indexes judiciously. Test first to determine

Indexes consume disk space and processing

quantifiable performance gains.

resources. Don’t add indexes unnecessarily.

Use the correct type of index.

Correct index usage maximizes performance. See

Table 8–1 for more details.

Use a separate tablespace(s) for indexes (separate

Table and index data may have different storage

from tablespaces used for tables).

and/or backup and recovery requirements. Using

separate tablespaces lets you manage indexes

separately from tables.

When creating primary-key constraints or unique-

Doing this separates the table and index data,

key constraints, specify the tablespace for the

which may have different storage and/or backup

index.

and recovery requirements.

Let the index inherit its storage properties from the

This makes it easier to manage and maintain index

tablespace.

storage.

Use consistent naming standards.

This makes maintenance and troubleshooting

easier.

Don’t rebuild indexes unless you have a solid

Rebuilding indexes is generally unnecessary unless

reason to do so.

an index is corrupt or you want to move an index

to different tablespace.

Monitor your indexes, and drop indexes that aren’t

Doing this frees up physical space and improves

used.

the performance of Data Manipulation Language

(DML) statements.

Before dropping an index, consider marking it as

This allows you to better determine if there are any

unusable or invisible.

performance issues before you drop the index.

These options let you rebuild or re-enable the

index without requiring the Data Definition

Language (DDL) creation statement.

Create indexes on foreign-key columns.

Foreign-key columns are usually included in the

WHERE clause when joining tables and thus improve

performance of SQL SELECT statements. This can

also improve performance when you’re deleting a

parent row and Oracle needs to look for any

records in a child table.

174

CHAPTER 8 ■ INDEXES

Refer to these guidelines as you create and manage indexes in your databases. These recommendations are intended to help you correctly use index technology.

Creating Indexes

When you think about creating tables, you must think about the corresponding index architecture.

Creating the appropriate indexes and using the correct index features usually results in dramatic performance improvements. Likewise, creating indexes on the wrong columns or using the features in the wrong situations can cause dramatic performance degradation.

As a DBA, you need to be aware of the types of indexes available with Oracle and when to use them.

Creating indexes and implementing specific features are discussed in the next several subsections. The B-tree index is the most used and default index type and therefore is first in line.

Creating B-tree Indexes

The default index type in Oracle is a B-tree (balanced tree) index. To create a B-tree index on an existing table, use the CREATE INDEX statement. This example creates an index on the D_SOURCES table, specifying D_SOURCE_ID as the column:

SQL> create index d_sources_idx1 on d_sources(d_source_id);

By default, Oracle tries to create an index in your default tablespace. Sometimes that may be the desired behavior. But often, for manageability reasons, you want to create the index in a specific tablespace. Use the following syntax to instruct Oracle to build an index in a specific tablespace: SQL> create index d_sources_idx1 on d_sources(d_source_id) tablespace dim_index;

■
Tip
If you don’t specify any physical storage properties for an index, the index inherits its properties from the tablespace in which it’s created. This is usually an acceptable method for managing index storage.

Figure 8–1 shows the balanced, tree-like structure of a B-tree index created on a first-name column.

This type of index has a hierarchical tree structure. When Oracle accesses the index, it starts with the top node called the root (or header) block. It uses this block to determine which second-level block (often referred to as a branch block) to read next. The second-level block points to several third-level leaf nodes that contain a ROWID and the name value. In this structure, it will take three I/O operations to find the ROWID. Once the ROWID is determined, Oracle will use it to read the table block that contains the ROWID.

175

CHAPTER 8 ■ INDEXES

Figure 8–1.
Oracle B-tree heirarchical index structure

The index definition is associated with a table and column(s). The index structure stores a mapping of a row’s ROWID and the column data on which the index is built. A ROWID usually uniquely identifies a row within a database and contains information to physically locate a row (datafile, block, and row position within block).

Creating Concatenated Indexes

Oracle allows you to create an index that contains more than one column. Multicolumn indexes are known as
concatenated indexes
. These indexes are especially effective when you often use multiple columns in the WHERE clause when accessing a table.

For the examples in this section, the underlying table is created as follows: create table inv(

inv_name varchar2(30)

,cust_name varchar2(30));

Now, suppose you have this scenario, where two columns from the same table are used in the WHERE

clause:

SQL> select cust_name from inv where inv_name='Mouse' and cust_name='Bob'; Because both INV_NAME and CUST_NAME are often used in WHERE clauses for retrieving data, it may be efficient to create a concatenated index on the two columns:

SQL> create index inv_idx3 on inv(inv_name, cust_name);

Often, it’s not clear whether a concatenated index is more efficient than a single-column index. For the previous SQL statement, you may wonder whether it’s more efficient to create two single-column index on INV_NAME and CUST_NAME, such as

SQL> create index idx1 on inv(inv_name);

SQL> create index idx2 on inv(cust_name);

However, if you’re consistently selecting the columns that appear in the WHERE clause, then the optimizer will most likely use the concatenated index and not use the single-column indexes. Using a 176

CHAPTER 8 ■ INDEXES

concatenated index in these situations is usually much more efficient. You can verify that the optimizer chooses the concatenated index by generating an explain plan. For example: SQL> set autotrace trace exlain;

SQL> select cust_name from inv where inv_name='Mouse' and cust_name='Bob'; Here’s some sample output indicating that the optimizer uses the concatenated index on INV_IDX3

to retrieve data:

-----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 34 | 1 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| INV_IDX3 | 1 | 34 | 1 (0)| 00:00:01 |

-----------------------------------------------------------------------------

In older versions of Oracle (circa v8), the optimizer would use a concatenated index only if the leading edge column(s) appeared in the WHERE clause. In modern versions, the optimizer uses a concatenated index even if the leading edge column(s) aren’t present in the WHERE clause. This ability to use an index without reference to leading edge columns is known as the
skip-scan
feature.

A concatenated index that is used for skip-scanning is more efficient than a full-table scan.

However, you should try to create concatenated indexes that use the leading column. If you’re consistently using only a lagging edge column of a concatenated index, then consider creating a single-column index on the lagging column.