sql – Oracle slow RANK function

sql – Oracle slow RANK function

As was mentioned in the comments, with your analytic function in the view, Oracle cant take any shortcuts (predicate pushing) because

  • in your view, you have created an agreement with Oracle: whenever the view is accessed the RANK should be based on all of the rows in the table – no WHERE clause was specified
  • when querying a view, an external WHERE clause should never affect how a row generated by the view looks, but only whether or not that row is kept or not
  • analytic functions look at other rows to generate a value so if you change those rows (filtering) you can change the value – pushing a predicate could easily affect the values generated by these functions
  • if this could happen, your view result could become very inconsistent (just depending on how the optimizer chose to evaluate the query)

So, based on the details youve provided, your query needs to be evaluated like this:

SELECT * 
  FROM (
         SELECT
                RANK() OVER (PARTITION BY FIELD1 ORDER BY FIELD2) RANK, 
                FIELD2, 
                FIELD3
           FROM TABLE1
       ) myview
 WHERE <condition>; -- rankings are not affected by external conditions

and not this:

SELECT * FROM (
    SELECT 
           RANK() OVER (PARTITION BY FIELD1 ORDER BY FIELD2) RANK, 
           FIELD2, 
           FIELD3
      FROM TABLE1
     WHERE FIELD3 IN (a,b,c) -- ranking is affected by the conditions
)

So, is there a way to make this faster? Maybe.

  1. If the table is partitioned, theres the thought of using parallel query.
  2. Could an index help?

Not in the usual sense. Since there are no conditions in the view itself, it will do a full table scan to consider all of the rows for the rankings and by the time the WHERE clause is applied, its too late to use an index for filtering.

However, if you had an index that covered the query, that is, have an index on just the columns being used (e.g. FIELD1, FIELD2, FIELD3 in that order), an index could be used as a smaller version of the table (instead of FULL TABLE SCAN the plan would show INDEX FAST FULL SCAN.) As a bonus, since its already sorted, it could be efficient at working out the partitions on FIELD1 and then ordering on FIELD2 within each partition.

  1. Another option would be to make this a materialized view instead, but if your data is changing often, it could be a pain to keep current.
  2. One final thought would be something that is similar to the poor mans partitioning used before the days of the partitioning option.
    (Sorry I cant find a good link that describes this, but maybe you have heard of it before.)

This is really only an option if:

  1. your partitioning column has a relatively small number of distinct values
  2. those values dont change
  3. you know what partition values you can use to isolate the data on in your query
  4. Oracle is willing to push the predicate when its safe to do so

Given that Oracle seems adverse to pushing the predicate when analytic functions are involved, Im not giving this a high probability of success.

If you want more info on that, let me know.

sql – Oracle slow RANK function

Related posts

Leave a Reply

Your email address will not be published.