Index Creation Guide Lines

Listed next are general guidelines for deciding which columns to index.
• Define a primary key constraint for each table that results in an index
automatically being created on the columns specified in the primary key (see
Recipe 2-3).
• Create unique key constraints on non-null column values that are required to be
unique (different from the primary key columns). This results in an index
automatically being created on the columns specified in unique key constraints
(see Recipe 2-4).
• Explicitly create indexes on foreign key columns (see Recipe 2-5).
• Create indexes on columns used often as predicates in the WHERE clause of
frequently executed SQL queries.
After you have decided to create indexes, we recommend that you adhere to index creation
standards that facilitate the ease of maintenance. Specifically, follow these guidelines when creating an
index:
• Use the default B-tree index unless you have a solid reason to use a different
index type.
• Create a separate tablespace for the indexes. This allows you to more easily
manage indexes separately from tables for tasks such as backup and recovery.
• Let the index inherit its storage properties from the tablespace. This allows you to
specify the storage properties when you create the tablespace and not have to
manage storage properties for individual indexes.
• If you have a variety of storage requirements for indexes, then consider creating
separate tablespaces for each type of index—for example, INDEX_LARGE,
INDEX_MEDIUM, and INDEX_SMALL tablespaces, each defined with storage
characteristics appropriate for the size of the index.


Listed next is a sample script that encapsulates the foregoing recommendations from the prior two
bulleted lists:
CREATE TABLE cust(
cust_id NUMBER
,last_name VARCHAR2(30)
,first_name VARCHAR2(30));
--
ALTER TABLE cust ADD CONSTRAINT cust_pk PRIMARY KEY (cust_id)
USING INDEX TABLESPACE reporting_index;
--
ALTER TABLE cust ADD CONSTRAINT cust_uk1 UNIQUE (last_name, first_name)
USING INDEX TABLESPACE reporting_index;
--
CREATE TABLE address(
address_id NUMBER,
cust_id NUMBER
,street VARCHAR2(30)
,city VARCHAR2(30)
,state VARCHAR2(30))
TABLESPACE reporting_data;
--
ALTER TABLE address ADD CONSTRAINT addr_fk1
FOREIGN KEY (cust_id) REFERENCES cust(cust_id);
--
CREATE INDEX addr_fk1 ON address(cust_id)
TABLESPACE reporting_index;
In the prior script, two tables are created. The parent table is CUST and its primary key is CUST_ID. The
child table is ADDRESS and its primary key is ADDRESS_ID. The CUST_ID column exists in ADDRESS as a foreign
key mapping back to the CUST_ID column in the CUST table.

*****************************************************************

Index creation guidelines

• Add indexes judiciously. Test first to determine quantifiable performance gains. Indexes consume disk space and processing resources. Don’t add indexes unnecessarily.

• Use the correct type of index. Correct index usage maximizes performance.
• Use consistent naming standards. This makes maintenance and troubleshooting easier.

• Monitor your indexes, and drop indexes that aren’t used. Doing this frees up physical space and improves the performance of Data Manipulation Language (DML) statements.
• Don’t rebuild indexes unless you have a solid reason to do so.

• Rebuilding an index is generally unnecessary unless the index is corrupt or you want to change a physical characteristic (such as the tablespace) without dropping the index.
• Before dropping an index mark it as invisible or unusable. This allows you to better determine if there are any performance issues before you drop the index.These options let you rebuild or re-enable the index without requiring the Data Definition Language (DDL) index creation statement
• Consider creating concatenated indexes that result in only the index structure being required to return the result set.
• Consider creating indexes on columns used in the ORDER BY, GROUP BY, UNION, or DISTINCT clauses.