In this short post I would like to point out a non-obvious issue that one of my customers recently hit. On the one hand, it’s a typical case where the query optimizer generates a different (suboptimal) execution plan even though nothing relevant (of course, at first sight only) was changed. On the other hand, in this case after some time the query optimizer automatically gets back to the original (optimal) execution plan.

Let’s have a look at the issue with the help of a test case…

The test case is based on a range partitioned table:

CREATE TABLE t
PARTITION BY RANGE (d) 
(
  PARTITION t_q1_2019 VALUES LESS THAN (to_date('2019-04-01','yyyy-mm-dd')),
  PARTITION t_q2_2019 VALUES LESS THAN (to_date('2019-07-01','yyyy-mm-dd')),
  PARTITION t_q3_2019 VALUES LESS THAN (to_date('2019-10-01','yyyy-mm-dd')),
  PARTITION t_q4_2019 VALUES LESS THAN (to_date('2020-01-01','yyyy-mm-dd'))
)
AS
SELECT rownum AS n, to_date('2019-01-01','yyyy-mm-dd') + rownum/(1E5/364) AS d, rpad('*',10,'*') AS p
FROM dual
CONNECT BY level <= 1E5

The partitioned table has a global partitioned index (but the behaviour would be the same with a non-partitioned index):

CREATE INDEX i ON T (n) GLOBAL PARTITION BY HASH (n) PARTITIONS 16

The query hitting the issue contains a MIN (or MAX) function:

SELECT min(n) FROM t

Its execution plan is the following and, as expected, uses the MIN/MAX optimization:

--------------------------------------------
| Id  | Operation                   | Name |
--------------------------------------------
|   0 | SELECT STATEMENT            |      |
|   1 |  SORT AGGREGATE             |      |
|   2 |   PARTITION HASH ALL        |      |
|   3 |    INDEX FULL SCAN (MIN/MAX)| I    |
--------------------------------------------

One day the data stored in the oldest partition is no longer needed and, therefore, it’s dropped (a truncate would lead to the same behaviour). Note that to avoid the invalidation of the index, the UPDATE INDEXES clause is added:

ALTER TABLE t DROP PARTITION t_q1_2019 UPDATE INDEXES

After that operation the query optimizer generates another (suboptimal) execution plan. The index, since it’s valid, it’s used. But, the MIN/MAX optimization is not:

---------------------------------------
| Id  | Operation              | Name |
---------------------------------------
|   0 | SELECT STATEMENT       |      |
|   1 |  SORT AGGREGATE        |      |
|   2 |   PARTITION HASH ALL   |      |
|   3 |    INDEX FAST FULL SCAN| I    |
---------------------------------------

And, even worse (?), few hours later the query optimizer gets back to the original (optimal) execution plan.

The issue is caused by the fact that, as of version 12.1.0.1, Oracle Database optimizes the way DROP/TRUNCATE PARTITION statements that use the UPDATE INDEXES clause are carried out. The index maintenance, to make the DROP/TRUNCATE PARTITION statements faster, is delayed and decoupled from the execution of the DDL statement itself. It’s done asynchronously. For detailed information about that feature have a look to the documentation.

To avoid the issue, you have to make sure to immediately carry out the index maintenance after the execution of the DROP/TRUNCATE PARTITION statement. For that purpose, you can run the following SQL statement:

execute dbms_part.cleanup_gidx(user, 'T')

In summary, even though an index is valid and can be used by some row source operations, if it contains orphaned index entries caused by the asynchronously maintenance of global indexes, it cannot be used by INDEX FULL SCAN (MIN/MAX). A final remark, the same is not true for the INDEX RANGE SCAN (MIN/MAX). In fact, that row source operation can be carried out also in case orphaned index entries exist.