title: Oracle pagination query and user defined function - slow execution
body: <p>My query is like this:</p>
  
  <pre><code>   select id, fn_calc(col)
     from table_a
     order by id
     offset 1483800 rows fetch next 100 rows only;
  </code></pre>
  
  <p>Note that the paging offset is extremely high - <code>table_a</code> has about 1,500,000 rows.</p>
  
  <p>The above query takes very long, but when <code>fn_calc(col)</code> is replaced by <code>col</code>, query speed is satisfactory - at least 5 times faster. But when the offset is 0 or 100, two queries are almost equally fast. Why this difference?</p>
  
  <p>Possible reasons I can think of:</p>
  
  <ul>
  <li>Oracle executes <code>fn_calc()</code> 1483900 times, although it is logically not neccessary. (It is enough to call it only 100 times)</li>
  <li>The calling cost for a user function in a query is very high.</li>
  </ul>
  
  <p>I'm using Oracle 12c on ExaData.</p>
  
  <p>Any suggestion can help.</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>When the above query is changed as follows:</p>
  
  <pre><code>   select id, fn_calc(col)
     from
     (
       select id, col
       from table_a
       order by id
       offset 1483800 rows fetch next 100 rows only
     )
     order by id
  </code></pre>
  
  <p>The query speed is comparable to the case when fn_calc() is not called at all.</p>
  
  <p>Why this?</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>The execution plans are as follows: (Sorry, currently only I have is SQLDevelper, so I had to capture the result.)</p>
  
  <p>The first query:</p>
  
  <p><a href="https://i.stack.imgur.com/JJ6G7.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JJ6G7.png" alt="First query"></a></p>
  
  <p>The second query (which uses subquery):</p>
  
  <p><a href="https://i.stack.imgur.com/pwKCJ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/pwKCJ.png" alt="Second query (with subquery)"></a></p>
  
  <p><strong>To down-voters and close-voters:</strong> Please specify your concerns about this question before you vote. I'll update my question accordingly. This question is about the real and serious problem to me. Please do not deprive the chance to get help.</p>