title: Best practice for pagination in Oracle?
body: <p>Problem: I need write stored procedure(s) that will return result set of a single page of rows <strong>and</strong> the number of total rows.</p>
  
  <p>Solution A: I create two stored procedures, one that returns a results set of a single page and another that returns a scalar -- total rows. The Explain Plan says the first sproc has a cost of 9 and the second has a cost of 3.</p>
  
  <pre><code>SELECT  *
  FROM    ( SELECT ROW_NUMBER() OVER ( ORDER BY D.ID DESC ) AS RowNum, ...
          ) AS PageResult
  WHERE   RowNum &gt;= @from
      AND RowNum &lt; @to
  ORDER BY RowNum
  
  SELECT  COUNT(*)
  FROM    ...
  </code></pre>
  
  <p>Solution B: I put everything in a single sproc, by adding the same <code>TotalRows</code> number to <em>every</em> row in the result set. This solution feel hackish, but has a cost of 9 and only one sproc, so I'm inclined to use this solution.</p>
  
  <pre><code>SELECT * 
  FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY D.ID DESC  ) RowNum, COUNT(*) OVER () TotalRows,
  WHERE RowNum &gt;= from
          AND RowNum &lt; to
  ORDER BY RowNum;
  </code></pre>
  
  <p>Is there a best-practice for pagination in Oracle? Which of the aforementioned solutions is most used in practice? Is any of them considered just plain wrong? Note that my DB is and will stay relatively small (less than 10GB).</p>
  
  <p>I'm using Oracle 11g and the latest ODP.NET with VS2010 SP1 and Entity Framework 4.4. I need the final solution to work within the EF 4.4. I'm sure there are probably better methods out there for pagination in general, but I need them working with EF.</p>