How to create concatenated index and how it works

Suppose you have a combination of columns (from the same table) that are often used in the WHERE clause of several SQL queries. For example, you use LAST_NAME in combination with FIRST_NAME to identify a customer:

select last_name, first_name
from cust
where last_name = 'SMITH'
and first_name = 'STEVE';

You wonder if it would be more efficient to create a single concatenated index on the combination of LAST_NAME and FIRST_NAME columns or if performance would be better if two indexes were created separately on LAST_NAME and FIRST_NAME.

So here is the solution, when frequently accessing two or more columns in conjunction in the WHERE clause, a concatenated index is often more selective than two single indexes. For this example, here’s the table creation script:

create table cust(
cust_id number primary key
,last_name varchar2(30)
,first_name varchar2(30));

Here’s an example of a concatenated index created on LAST_NAME and FIRST_NAME:

SQL> create index cust_idx1 on cust(last_name, first_name);

To determine whether the concatenated index is used, several rows are inserted (only a subset of the rows is shown here):

SQL> insert into cust values(1,'SMITH','JOHN');
SQL> insert into cust values(2,'JONES','DAVE');
..........
SQL> insert into cust values(3,'FORD','SUE');

Next, statistics are generated for the table and index:

SQL> exec dbms_stats.gather_table_stats(ownname=>'MV_MAINT',-
tabname=>'CUST',cascade=>true);

Now Autotrace is turned on so that the execution plan is displayed when a query is run:

SQL> set autotrace on;

Here’s the query to execute:

select last_name, first_name
from cust
where last_name = 'SMITH'
and first_name = 'JOHN';

Listed next is an explain plan that shows the optimizer is using the index:

The prior output indicates that an INDEX RANGE SCAN was used to access the CUST_IDX1 index. Notice that all of the information required to satisfy the results of this query was contained within the index. The table data was not required. Oracle accessed only the index.

One other item to consider: suppose you have this query that additionally selects the CUST_ID column:

select cust_id, last_name, first_name
from cust
where last_name = 'SMITH'
and first_name = 'JOHN';

If you frequently access CUST_ID in combination with LAST_NAME and FIRST_NAME, consider adding CUST_ID to the concatenated index. This will provide all of the information that the query needs in the index. Oracle will be able to retrieve the required data from the index blocks and thus not have to access the table blocks.

How It Works

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. Here are some factors to consider when using concatenated indexes:

• If columns are often used together in the WHERE clause, consider creating a concatenated index.

• If a column is also used (in other queries) by itself in the WHERE clause, place that column at the leading edge of the index (first column defined).

• Keep in mind that Oracle can still use a lagging edge index (not the first column defined) if the lagging column appears by itself in the WHERE clause (see the next few paragraphs here for details).

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. For example, say you have this query that uses the FIRST_NAME column

SQL> select last_name from cust where first_name='DAVE';

Here is the corresponding explain plan showing that the skip-scan feature is in play:

A concatenated index that is used for skip-scanning is more efficient than a full table scan. However, 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.