When a single index has multiple columns that are indexed, it is called a concatenated or composite index. While Oracle 9i’s introduction of skip-scan index access has increased the optimizer’s options when using concatenated indexes, you should be careful when selecting the order of the columns in the index. In general, the leading column of the index should be the one most likely to be used in WHERE clauses and also the most selective column of the set.
Prior to the introduction of skip-scan functionality, queries could only use the index if the leading column of the index was in the WHERE clause. Consider the example in the following listing where the EMP table has a concatenated index on EMPNO, ENAME, and DEPTNO. Note that EMPNO is the first part; ENAME is the second part; and DEPTNO is the third part. If you are not making use of the skip-scan functionality, Oracle will generally not use this index unless your WHERE clause specifies a value for the leading column (EMPNO).
Since ENAME is not the leading column of the index, the optimizer may elect not to use the index. Starting with the introduction of the skip-scan functionality in Oracle 9i, the optimizer may choose to use the index, even though an EMPNO value is not specified in the WHERE clause. Instead, the optimizer could choose to perform a fast full scan of the index or a full scan of the table.
The same holds true if the third column of the index is used in the WHERE clause:
In this listing, the WHERE clause specifies a value for the third column in the index. The optimizer may select to perform an index skip-scan access, an index fast full scan, or a full table scan. By creating the index, you have given the database more choices to consider when executing the query, hopefully improving overall performance. Note that the user’s code does not change; the optimizer is aware of the index and bases its decisions on the anticipated cost of each alternative.
In the following example, a part of the index is used. The leading column, EMPNO, is used as a limiting condition in the WHERE clause so that Oracle can use the index.
The two most common types of index scans are unique scans and range scans. In a unique scan, the database knows that the index contains a list of unique values. In a range scan, the database returns multiple values from the index according to the query criteria. In this example, the EMP_ID1 and EMP_ID2 indexes were not created as unique indexes. Oracle will perform a range scan when retrieving their data. To create a unique index, use the CREATE UNIQUE INDEX command when creating the index.
When you create a primary key or a UNIQUE constraint, Oracle automatically creates a unique index based on the columns you specify (unless the constraint is created with the DISABLE clause). If you create a multicolumn primary key, Oracle creates a concatenated index with the columns in the same order in which you specified them when creating the primary key.
Indexes like EMP_ID1 and EMP_ID2 provide Oracle with the ability to access a single row of data by supplying the ROWID of the individual row. The ROWID is a pointer directly to the physical location of the individual row.
TIP
Avoid hard-coding Oracle’s ROWID into specific code. The ROWID structure in the past has changed from version to version and will probably change again in future releases. I recommend never hard-coding a ROWID.
Leave a Reply