Oracle SQL Performance Tuning

Composite / Concatenated SQL Indexes

When a single index has multiple columns that are indexed, it is called a composite or concatenated index.

Prior to the introduction of skip-scan functionality, queries could only use the index if the leading column of the index was used 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).

select job, empno
from emp
where ename = 'JAY';

Since Ename is not the leading column of the index, the optimizer may elect not to use the index. 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:

select job, empno
from emp
where deptno = 30;

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 the 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.

Niemiec, R. J. (2007). Oracle Database 10g Performance Tuning Tips & Techniques: Oracle Press.

Leave a Reply

Your email address will not be published. Required fields are marked *

ERROR: si-captcha.php plugin: GD image support not detected in PHP!

Contact your web host and ask them to enable GD image support for PHP.

ERROR: si-captcha.php plugin: imagepng function not detected in PHP!

Contact your web host and ask them to enable imagepng for PHP.