Oracle Optimizer not using Index when the query has bind variables AND pagination clause

Hi,

We have a table that has nearly 21m rows.

Our application was not performing some queries and we checked the Query in SQL Developer.

We simulated the application by defining 3 bind variables for customer id, OFFSET and FETCH.

The query was taking nearly 35s to fetch the first 50 rows ( we passed 0 for OFFSET and 50 for FETCH).

We did multiple tests find that the Oracle optimizer isn’t using the Index when the Query has Bind variables with pagination clauses like OFFSET and FETCH.

We tried replacing OFFSET and FETCH with ROWNUM but still the optimizer isn’t using the Index. When we use hardcoded values for OFFSET and FETCH , the optimizer is using the Index.