Query with rownum got slow

I have got a table table with 21 millions records of which 20 millions meet the criterion col1= 'text'. I then started iteratively to set the value of col2 to a value unequal to NULL. After I have mutated 10 million records, the following query got slow, that was fast in the beginning:

SELECT T_PK
  FROM (SELECT T_PK
         FROM table
         WHERE col1= 'text' AND col2 IS NULL
         ORDER BY T_PK DESC)
WHERE ROWNUM < 100;

I noticed that as soon as I remove the DESC, the whole order by clause ORDER BY T_PK DESC or the whole outer query with the condition WHERE ROWNUM < 100 it is fast again (fast means a couple of seconds, < 10s).

The execution plan looks as follows:

where the index full scan descending index is performed on the PK of the table. Besides the index on the PK, I have an index defined on col2.

What could be the reason that the query was fast and then got slow? How can I make the query fast regardless of how many records are already set to non-null value?

Answer

For this query:

SELECT T_PK
FROM (SELECT T_PK
      FROM table
      WHERE col1= 'text' AND col2 IS NULL
      ORDER BY T_PK DESC
     ) t
WHERE ROWNUM < 100;

The optimal index is table(col1, col2, t_pk).

I think the problem is that the optimizer has a choice of two indexes — either for the where clause (col1 and — probably — col2) or one on t_pk. If you have a single index that handles both clauses, then performance should improve.

One reason that the DESC might make a difference is where the matching rows lie. If all the matching rows are in the first 100,000 rows of the table, then when you order descending, the query might have to throw out 20.9 million rows before finding a match.