I have a top N query that is giving me problems.

First of all, I have a query like the following:

```
select /*+ gather_plan_statistics */ * from 
(
  select rowid
  from payer_subscription ps
  where  ps.subscription_status = :i_subscription_status 
  and ps.merchant_id           = :merchant_id2
  order by transaction_date desc
) where rownum <= :i_rowcount; 

```

This query works well. It can very efficiently find me the top 10 rows for a massive data set, using an index on merchant\_id, subscription\_status, transaction\_date.

```
-------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |      1 |        |     10 |00:00:00.01 |       4 |
|*  1 |  COUNT STOPKEY                |             |      1 |        |     10 |00:00:00.01 |       4 |
|   2 |   VIEW                        |             |      1 |     11 |     10 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN DESCENDING| SODTEST2_IX |      1 |    100 |     10 |00:00:00.01 |       4 |
-------------------------------------------------------------------------------------------------------

```

As you can see the estimated actual rows at each stage are 10, which is correct.

Now, I have a requirement to get the top N records for a set of merchant\_Ids, so if I change the query to include two merchant\_ids, the performance tanks:

```
select /*+ gather_plan_statistics */ * from 
(
  select  rowid
  from payer_subscription ps
  where  ps.subscription_status = :i_subscription_status 
      and (ps.merchant_id = :merchant_id or 
           ps.merchant_id = :merchant_id2 )
  order by transaction_date desc
) where rownum <= :i_rowcount; 

    ----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |      1 |        |     10 |00:00:00.17 |     178 |       |       |          |
|*  1 |  COUNT STOPKEY          |             |      1 |        |     10 |00:00:00.17 |     178 |       |       |          |
|   2 |   VIEW                  |             |      1 |    200 |     10 |00:00:00.17 |     178 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY|             |      1 |    200 |     10 |00:00:00.17 |     178 |  2048 |  2048 | 2048  (0)|
|   4 |     INLIST ITERATOR     |             |      1 |        |  42385 |00:00:00.10 |     178 |       |       |          |
|*  5 |      INDEX RANGE SCAN   | SODTEST2_IX |      2 |    200 |  42385 |00:00:00.06 |     178 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------

```

Notice now that there are 42K rows coming out of the two index range scans - Oracle is no longer aborting the index range scan when it reaches 10 rows. What I thought would happen, is that Oracle would get at most 10 rows for each merchant\_id, knowing that at most 10 rows are to be returned by the query. Then it would sort that 10 + 10 rows and output the top 10 based on the transaction date, but it refuses to do that.

Does anyone know how I can get the performance of the first query, when I need to pass a list of merchants into the query? I could probably get the performance using a union all, but the list of merchants is variable, and could be anywhere between 1 or 2 to several 100.