title: What is the best way to ensure consistent ordering in an Oracle query?
body: <p>I have an program that needs to run queries on a number of very large Oracle tables (the largest with tens of millions of rows). The output of these queries is fed into another process which (as a side effect) can record the progress of the query (i.e., the last row fetched).</p>
  
  <p>It would be nice if, in the event that the task stopped half way through for some reason, it could be restarted. For this to happen, the query has to return rows in a consistent order, so it has to be sorted. The obvious thing to do is to sort on the primary key; however, there is probably going to be a penalty for this in terms of performance (an index access) versus a non-sorted solution. Given that a restart may never happen this is not desirable.</p>
  
  <p>Is there some trick to ensure consistent ordering in another way? Any other suggestions for maintaining performance in this case?</p>
  
  <p>EDIT: I have been looking around and seen "order by rowid" mentioned. Is this useful or even possible?</p>
  
  <p>EDIT2: I am adding some benchmarks:</p>
  
  <ul>
  <li>With no order by: 17 seconds. </li>
  <li>With order by PK: 46 seconds. </li>
  <li>With order by rowid: 43 seconds.</li>
  </ul>
  
  <p>So any order by has a savage effect on performance, and using rowid makes little difference. Accepted answer is - there is no easy way to do it.</p>