We recommend that you place indexes on primary key, unique key, and foreign key columns. This is
a good starting point. Furthermore, analyze slowly performing SQL statements to see which columns are used. This will provide you additional candidate columns for indexing. These indexing
recommendations lay the foundation for maximizing SQL query performance.
Now we turn our attention to figuring out which columns should be indexed.
For most applications you create indexes in the following situations:
Define a primary key constraint for each table: This results in an index
automatically being created on the columns specified in the primary key.
Create unique key constraints on columns that are required to be unique and are
different from the primary key columns: Each unique key constraint results in an
index automatically being created on the columns specified in the constraint.
Manually create indexes on foreign key columns: This is done for better
performance to avoid certain locking issues
Other Suitable Columns
When choosing an index, keep in mind this basic rule: the indexing strategy should be based on the
columns you use when querying the table. You can create more than one index on a table and have an
index contain multiple columns. You will make better decisions if you first consider what types of
queries you execute on a table. If you’ve identified a poorly performing SQL query, also consider creating
indexes for the following columns:
• Create indexes on columns used often as predicates in the WHERE clause; when
multiple columns from a table are used in the WHERE clause, consider using a
concatenated (multi-column) index.
• Create a covering index on columns used in the SELECT clause.
• Consider creating indexes on columns used in the ORDER BY, GROUP BY, UNION, or
DISTINCT clauses.
-> Create primary key constraints for all tables. This will automatically create a B-tree index (if
the columns in the primary key aren’t already
indexed).
-> Create unique key constraints where appropriate. This will automatically create a B-tree index (if
the columns in the unique key aren’t already
indexed).
-> 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.
-> Creating a B-tree index on foreign key columns also reduces locking issues when updating and
inserting into child tables.
B-tree indexes:
B-tree indexes are suitable for most applications
where you have high cardinality column values.
Bitmap indexes:
Consider using bitmap indexes in data warehouse environments.These indexes are ideal for low cardinality columns where the values aren’t updated often.
Bitmap indexes work well on foreign key columns on Star schema fact tables where you often run
queries that use AND and OR join conditions
- Doing this frees up physical space and improves the performance of data manipulation language (DML) statements.
The B-tree index is the default index type in Oracle. This index type is known as B-tree because the table
row identifier (ROWID) and associated column values are stored within index blocks in a balanced treelike
structure. Oracle B-tree indexes are used for the following reasons:
- Improving SQL statement performance.
- Enforcing uniqueness of primary key and unique key constraints.
- Reducing potential locking issues with parent and child table tables associated via primary and foreign key constraints
Query to find out the indexes on tables:
select index_name, index_type, table_name, tablespace_name, status
from user_indexes
where table_name in ('CUST','ADDRESS');
Here is some sample output:
INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME STATUS
-------------------- ---------- ---------- --------------- ----------
CUST_PK NORMAL CUST REPORTING_INDEX VALID
CUST_UK1 NORMAL CUST REPORTING_INDEX VALID
ADDR_FK1 NORMAL ADDRESS REPORTING_INDEX VALID
Run the following query to verify the columns on which the indexes are created:
select index_name, column_name, column_position
from user_ind_columns
where table_name in ('CUST','ADDRESS')
order by index_name, column_position;
Here is some sample output:
INDEX_NAME COLUMN_NAME COLUMN_POSITION
-------------------- -------------------- ---------------
ADDR_FK1 CUST_ID 1
CUST_PK CUST_ID 1
CUST_UK1 LAST_NAME 1
CUST_UK1 FIRST_NAME 2
To display the number of extents and space used, run the following query:
insert into cust values(1,'STARK','JIM');
insert into address values(100,1,'Vacuum Ave','Portland','OR');
Rerunning this query (that reports on segment usage) yields the following output:
SEGMENT_NAME SEGMENT_TYPE EXTENTS BYTES
-------------------- -------------------- ---------- ----------
CUST_PK INDEX 1 1048576
CUST_UK1 INDEX 1 1048576
ADDR_FK1 INDEX 1 1048576
Use the DBMS_METADATA.GET_DDL function to display an object’s DDL. Make sure you set the LONG variable to an appropriate value so that the returned CLOB value is displayed in its entirety.
For example,
SQL> set long 1000000
SQL> select dbms_metadata.get_ddl('INDEX','ADDR_FK1') from dual;
Here is the output:
DBMS_METADATA.GET_DDL('INDEX','ADDR_FK1')
--------------------------------------------------------------------------------
CREATE INDEX "MV_MAINT"."ADDR_FK1" ON
"MV_MAINT"."ADDRESS" ("CUST_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255
COMPUTE STATISTICS STORAGE(INITIAL 1048576
NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "REPORTING_INDEX"
- Here’s an example of making an index invisible:
SQL> alter index addr_fk1 invisible;
This code makes the index invisible to the query optimizer so that it can’t be used to retrieve rows for a query. However, the index structure is still maintained by Oracle as records are modified in the table. If you determine that the index was critical for performance, you can easily make it visible to the optimizer again via
SQL> alter index addr_fk1 visible;
- Your other option before dropping an index is to make it unusable.
SQL> alter index addr_fk1 unusable;
This code renders the index unusable, but doesn’t drop it. Unusable means that the optimizer won’t use
the index and Oracle won’t maintain the index as DML statements operate on its table. Furthermore, an
unusable index can’t be used internally to enforce constraints or prevent locking issues.
If you need to re-enable an unusable index, then you’ll have to rebuild it. Be aware that rebuilding a
large index can take a considerable amount of time and resources.
SQL> alter index addr_fk1 rebuild;
After you’re sure an index isn’t required, use the DROP INDEX statement to remove it.
This permanently drops the index. The only way to get the index back is to re-create it.
SQL> drop index addr_fk1;
Viewing Primary Key Constraint and Index Details
You can confirm the details of the index as follows:
select index_name, index_type, uniqueness
from user_indexes
where table_name = 'CUST';
Here is the output for this example:
INDEX_NAME INDEX_TYPE UNIQUENES
-------------------- --------------- ---------
CUST_PK NORMAL UNIQUE
Here is a query to verify the constraint information:
select constraint_name, constraint_type
from user_constraints
where table_name = 'CUST';
Here is the corresponding output:
CONSTRAINT_NAME CONSTRAINT_TYPE
-------------------- ---------------
CUST_PK P
Dropping Primary Key Constraint and Index
An index that was automatically created (when the primary key constraint was created) can’t be directly
dropped. In this scenario, if you attempt to drop the index
SQL> drop index cust_pk;
you’ll receive this error
ORA-02429: cannot drop index used for enforcement of unique/primary key
To remove the index, you must do so by dropping or disabling the primary key constraint. For
example, any of the following statements will drop an index that was automatically created when the
constraint was created:
SQL> alter table cust disable constraint cust_pk;
SQL> alter table cust drop constraint cust_pk;
SQL> alter table cust drop primary key;
When you drop or disable a primary key constraint, you have the option of not dropping the
associated index. Use the KEEP INDEX clause of the DROP/DISABLE CONSTRAINT clause to retain the index.
For example,
SQL> alter table cust drop constraint cust_pk keep index;
This code instructs Oracle to drop the constraint but keep the index. If you’re working with large
tables, you may want to disable or drop the constraint for performance reasons while loading or
manipulating the data. Dropping the index associated with a large table may take considerable time and
resources to re-create.
One other aspect to be aware of is that if a primary key or unique key is referenced by an enabled
foreign key, and you attempt to drop the constraint on the parent table
SQL> alter table cust drop primary key;
you’ll receive this error
ORA-02273: this unique/primary key is referenced by some foreign keys
In this situation you’ll need to first drop or disable the referenced foreign key, or use the CASCADE
clause to automatically drop the foreign key constraint when the primary key constraint is dropped or
disabled. For example,
SQL> alter table cust drop constraint cust_pk cascade;
SQL> alter table cust disable constraint cust_pk cascade;
SQL> alter table cust drop primary key cascade
