Hello, today we are going to share a recent optimization case. While reviewing performance issues for the optimization of an Oracle environment we detected a query that took 29 minutes to execute, we immediately started to review its execution plan.
Initial implementation 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, you have a correct execution plan: there is an index by the two columns that are part of the WHERE.
The execution plan is:
Although it is entered by index, the cost of the consultation is extremely high. Este hecho provoca que tarde mucho tiempo en resolver la misma.
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 full picture of the scenario, it is necessary to comment that the STATUS column has a very small cardinality, and that the values that it filters in where retrieve almost all the rows of the table.
The cardinality in a table is the number of distinct values that the table has. A low cardinality indicates that there are few distinct values with many rows for each value, and a high cardinality means that there are many distinct 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 are the maximum number of distinct values for the column, and each value has only 1 row.
In this particular case, we find a table with low cardinality, i.e., few distinct values for each value. Running a query to know the number of rows by value in the IDENT table returns the following result:
Index BITMAP
In the result of the query it is clear that it tries to return most of the rows of the table with the values that appear in the WHERE filter. In this scenario, at first, it seems clear that an improvement of the query execution can come from the replacement of the index by a BITMAP index, to take advantage of the advantages of this type of indexes on low cardinality columns with respect to the B*tree indexes that are created by default. More information on BITMAP indexes is available on this page.
But after creating the BITMAP index, we found that there was no 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 as 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 the query improve?
The improvement is due to the fact that Oracle indexes do not store 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 is as follows:
We hope you found it useful, see you in future posts. You can check out the Oracle entries we have published at this link.
Database Equipment