Composite Indexing Strategy

Index is a database object which contains an entry of each record that appears in an Indexed column to provide faster access of data. Indexes are used in a View or a Table. Knowing the concept of Index in detail is very crucial to database administrators, database developers, and anyone who works with the database. The proper use of Index makes your query and database perform much faster. Index can hurt the query performance and add overhead to database if not used correctly.

With this article, you will walk away with learning the proper design of composite Index on a Oracle database management system. The concept presented here applies to most of the popular DBMS available in the market today. I also have tested these composite indexing principle with MySQL and SQLServer and it works the way it works in Oracle.

You can also visit my article on Index, which talks about the characteristics, types, maintenance, restrictions and industries best practices. In general, you need to know these two questions before we dive deep into composite Index.

When TO use Index?

1. Frequently searched column
2. When selecting 10% or less data
3. When recommended by SQL tuning advisor
4. Query with JOIN and WHERE clause

When NOT to use Index?

1. Frequently updated or deleted column
2. When selecting more than 10% of data
3. When the database engine ignores the Index use

What is a composite Index? An Index that uses more than one column is called a composite Index. Most popular database systems today lets you create composite Index. Composite Index is also referred to as a Multi-column Index. We will use our standard employee table to explain composite Index and performance we can gain from the Index.

Below is the employee table current structure that has 14 standard employees’ data that we will be using in our composite Index examples through this article. Column empno has a primary key that has Index by default.

CREATE TABLE baniya.employee (
    empno    NUMBER(4, 0),
    ename    VARCHAR2(10),
    job      VARCHAR2(9),
    mgr      NUMBER(4, 0),
    hiredate DATE,
    sal      NUMBER(7, 2),
    comm     NUMBER(7, 2),
    deptno   NUMBER(2, 0),
    CONSTRAINT pk_emp PRIMARY KEY(empno));

Using the employee table, we will write various queries to demonstrate how efficiently we can retrieve data from the table using the where predicates.

Query 1: Most frequently used query.

Select * from baniya.employee;

The query does not use any predicate and select everything from employee table. Oracle does a Full scan on the employee table to retrieve and display data. See Explain Plan below.

Explain Plan:

Query 2: Using a single column on a WHERE clause.

Select  * from baniya.employee where empno =7900;

The above query uses empno column on where clause which makes use of Index for faster processing. Why? The table has primary key on empno which has an Index. .The execution plan uses Unique Index Scan to retrieve data. See Explain Plan below.

Explain Plan:

Query 3: Query using more than one column on a WHERE clause.

Select * from baniya.empoyee where deptno =10 and sal > 55 and comm < 100;

Explain Plan:

Employee number is the only column that is Indexed and the query does not use Indexed column, therefore the query is performing a full table scan (FTS) on an employee table. The full table scan makes the query inefficient and performs the query execution slow. How do you make this query efficient? To make the query efficient, we need Index on all the columns that are on where clause.

Let’s create composite index on employee table.
Syntax:

CREATE INDEX composite_index ON baniya.employee(
            deptno,sal, comm);

Does order of column matter while Index creation? Yes, it does, I would add a column with the high carnality or selectivity the leading column in a composite Index. The high selectivity column reduces the disk I/O thus making a query run faster.We have just created a composite Index on columns deptno, sal and comm. I didn't follow the selectivity or cardinality rule while creating the composite Index but this isn't going to impact in a small table like ours.The above query should now use the composite Index that we created. How does the explain looks like now?

Explain Plan:

Creating Index intelligently makes your query run faster. I would create composite Index only if the query is being used frequently. I would not create if you are running a query once twice for research or for your  data curiosity. Do not forget Index comes with a price and degrades the performance of Insert statements and also make use of additional disk space. The faster storage disk space is not cheap and is costly to maintain too.

The query that has all the columns from composite Index on where clause uses the composite

Index and we proved it. What other queries can benefit from the composite Index we added? Let’s explore more on how the queries that uses composite Index columns. All the possible queries along with the Index use is listed below.

SELECT * 
FROM   baniya.employee 
WHERE  deptno = 10 
       AND sal > 55 
       AND comm < 100; --Yes, it does 

SELECT * 
FROM   baniya.employee 
WHERE  sal = 55 
       AND comm = 100 
       AND deptno = 10; --Yes, it does

SELECT * 
FROM   baniya.employee 
WHERE  deptno = 10 
       AND sal > 55; --Yes, it does

 SELECT * 
FROM   baniya.employee 
WHERE  sal > 55 
       AND deptno = 10; --Yes, it does

SELECT * 
FROM   baniya.employee 
WHERE  deptno = 10 
       AND comm < 100; --yes, it does 

SELECT * 
FROM   baniya.employee 
WHERE  comm < 100 
       AND deptno = 10; -- yes, it does 

SELECT * 
FROM   baniya.employee 
WHERE  sal > 55 
       AND comm < 100; --No, it does not 

SELECT * 
FROM   baniya.employee 
WHERE  comm < 100 
       AND sal > 55; -- No, it does not 

SELECT * 
FROM   baniya.employee 
WHERE  deptno = 10; -- Yes, it does 

SELECT * 
FROM   baniya.employee 
WHERE  sal > 55; -- No, it does not 

SELECT * 
FROM   baniya.employee 
WHERE  comm < 100; -- No, it does not. 

What can we  conclude now?  The leading column must be present in a query to utilize composite Index. The column order does not matter on  WHERE clause but it matters while designing and creating a composite Index. The column that has most selectivity should be a leading column on composite Index.