In this article, we’ll look at how an index can improve the query performance.
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.
SELECT customer_id,first_name,last_name, max_credit, created_date FROM customer;
Here’s a sample of the table.
[table id=38 /]
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:
SELECT customer_id,
first_name,
last_name,
max_credit,
created_date
FROM customer
WHERE created_date = (
SELECT MIN(created_date)
FROM customer
)
ORDER BY last_name;
The output looks like this:
[table id=39 /]
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:
SELECT * FROM TABLE(dbms_xplan.display);
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:
CREATE INDEX idx_cust_cdate ON customer (created_date);
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.
