Example of Improving Query Performance with Indexes

Example of Improving Query Performance with Indexes
Rate this post

In this article, we’ll look at how an index can improve the query performance.

Introduction 

Indexes in Oracle and other databases are objects that store references to data in other tables. They are used to improve the query performance, most often the SELECT statement.

They aren’t a “silver bullet” – they don’t always solve performance problems with SELECT statements. However, they can certainly help.

Let’s consider this on a particular example.

Example

For this example, we’re going to use a single table called Customer that contains such columns as ID, First Name, Last Name, maximum credit value, created date value, and other columns that we won’t use.

Here’s a sample of the table.

CUSTOMER_IDFIRST_NAMELAST_NAMEMAX_CREDITCREATED_DATE
1CarolynHudson656713/Jun/17
2JudyWard253515/Dec/16
3JamesMedina273826/May/17
4PeterGreene27003/Dec/16
5SeanOrtiz245217/May/17

Now, we are going to find the following:

  • Who of the clients were added to the table at the same date with the first customers
  • Clients filtered by last_name in an ascending order
  • Display the customer ID, First Name, Last Name, max credit, and created date

To do this, create the following query:

The output looks like this:

CUSTOMER_IDFIRST_NAMELAST_NAMEMAX_CREDITCREATED_DATE
654424GeorgeAdams955222/Nov/16
515704CherylAdams581222/Nov/16
730835JudithAlexander491322/Nov/16
765508BonnieAlexander404222/Nov/16
459128WandaAlexander298722/Nov/16
469019RoseAlexander954422/Nov/16
372243JoyceAlexander136422/Nov/16
215160DennisAllen427422/Nov/16
658203ClarenceAlvarez390122/Nov/16
654855CarolynAlvarez366322/Nov/16

It shows the data we want.

Performance before Index Applied

Now, let’s look at the explain plan for this query. I obtained this by running EXPLAIN PLAN FOR for the SELECT statement and the following command:

You can get a similar output result using the Explain Plan feature inside your IDE as well.

We can see that it performs a Table Access Full at two points, namely in the subquery and in the outer query. This is because there are no indexes on the table to be used.

It has a cost of 2934. When I ran it, the query fetched 785 rows in 1.9 seconds. It might seem to be quickly but this is just an example that we can improve on. Queries in real systems can take much longer.

One way we could improve the performance of this query is to add an index to the created_date column. This column is used both in the WHERE clause of the outer query and in the MIN function of the inner query.

Add Index 

We can add an index to this table to improve the query performance. This index will be stored in the created_date column so that the code can look like this:

Now, the index is created on this column only. It should give us a performance improvement in our query, but we’ll have to check it first.

We’ve created a b-tree index, which is probably all we need on this column. We’ll confirm it in the explain plan shortly. I’ve written a guide on Oracle indexes including how to know what index type to be used, as well as lots of other valuable information.

Performance After Index Added

Let’s re-run the explain plan on this query.

We can see that an index has been used at the last step. It shows that a Full Scan has been executed with the idx_cust_cdate index, which is the one we have just created.

It also shows an overall cost of 1472 and fetches the 785 records in 0.9 seconds.

The runtime has only slightly improved (from 1.9 to 0.9 seconds), but that’s about a 50% improvement just by adding the index to this small dataset.

As mentioned earlier, real queries will be more complicated than this one and it will take more time to be executed. But, this is an example how an index can improve the query plan and the query runtime.

Ben Brumm

Ben Brumm

Ben Brumm is a software consultant with over 10 years experience in the IT industry. He is also the founder of DatabaseStar.com, a website that teaches database developers how to improve their skills and their career.
Ben Brumm