CODEWITHSUNDEEP
oracle-databasetop-n
Stephen ODonnell
Stephen ODonnell

Reputation: 4466

Top N query performance when accessing a list of IDs

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.

Upvotes: 2

Views: 425

Answers (3)

scmehetio
scmehetio

Reputation: 11

There are many cases where use_concat is ignored. See: MOS Note: USE_CONCAT hint on different versions (Doc ID 259741.1)

I have had success in 10.2.0.4, 11.2.0.1 with OR_EXPAND where USE_CONCAT will not work. /*+ OR_EXPAND( alias column_name ) */

Documented here: http://www.hellodba.com/reader.php?ID=199&lang=EN

Upvotes: 1

schurik
schurik

Reputation: 7928

I'm not sure if this helps, but you cah try to replace the OR operator with IN:

and ps.merchant_id IN (:merchant_id, :merchant_id2)

Upvotes: 0

jva
jva

Reputation: 2807

You can use --+ use_concat hint to make Oracle execute query as if it was a UNION ALL.

From documentation:

The USE_CONCAT hint instructs the optimizer to transform combined OR-conditions in the WHERE clause of a query into a compound query using the UNION ALL set operator. Without this hint, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them. The USE_CONCAT hint overrides the cost consideration.

Upvotes: 1

Related Questions