Execution plan. Optimize query in Oracle

Hello, today  we are going to share a recent case of optimization. While reviewing performance issues for the optimization of an Oracle environment we detected a query that took 29 minutes to execute,  we immediately began to review its execution plan.

Initial execution plan

The query in question is:

SELECT codprs, codnum, tipdcmide, numd, nombre, apell, apel2, estado, fecha FROM ident WHERE estado IN ('1', '2', '3', '5') AND numd IS NOT NULL;

Apparently, it has a correct execution plan: there is an index by the two columns that are part of the WHERE.

The execution plan is:

Oracle query execution plan

Although it is entered by the index, the cost of the consultation is extremely high. This fact causes it to take a long time to solve it.

The index through which it enters, IX_IDENT_CODNUMDO, has the following syntax:

CREATE INDEX "USER"."IX_IDENT_CODNUMDO" ON "USER"."IDENT" ("ESTADO", "NUMD")

Cardinality

To have the complete picture of the scenario, it is necessary to comment that the STATE column has a very small cardinality, and that the values it filters in the where retrieve almost all the rows of the table.

Cardinality in a table is the number of different values that the table has. A low cardinality indicates that there are few distinct values, and with many rows for each value, and a high cardinality, it means that there are many different values with few rows for each value. The best example of high cardinality in a table would be the Primary Key, which implies that all rows must be distinct, so there is the maximum number of different values for the column, and each value has only 1 row.

In this specific case, we find a table with low cardinality, that is, few different values for each value. Running a query to know the number of rows per value in the IDENT table returns the following result:

number of rows in Oracle

BITMAP Index

The query result clearly shows that it is trying to return most of the rows in the table with the values that appear in the WHERE filter. In this scenario, at first, it seems clear that an improvement in the execution of the query can come from the replacement of the index by a BITMAP index, to take advantage of this type of indices in columns of low cardinality with respect to the B*tree indices that are created by default. You have more information about BITMAP indexes on this page.

But after creating the BITMAP index, we found that there was no major substantial improvement, as the query still took more than 20 minutes.

We found a solution by changing the columns of the order index, creating the index with the columns in the opposite direction to how they are in the WHERE clause.

We create the following index:

CREATE BITMAP INDEX "USER"."IX_IDENT_CODNUMDO" ON "USER"."IDENT" ("NUMD", "ESTADO" )

With this new index, the query execution plan changes radically. It takes less than 4 minutes.

Why does query improve?

The improvement is given because Oracle indexes do not save null values. In this way, when evaluating the WHERE part, the NUMD column is evaluated first. By the time the second column is to be evaluated, the optimizer already finds the values placed to return the result.

The execution plan looks like this:

Final execution plan

We hope it has served you, see you in future posts. You can look at the Oracle posts that we have published in this link.

Database Team

Comments are closed.