title: Optimize Oracle order by query
body: <p>I have a table with around 5 million rows that look like this:  </p>
  
  <p><strong>Erp_in:</strong></p>
  
  <pre><code>corr_id varchar(50) (almost Unique)
  corr_type nvarchar(1) (4 distinct values)
  interface varchar(20) (around 10 distinct values)
  indate DateTime
  </code></pre>
  
  <p>With 3 different index on (corr_id, interface and indate)<br>
  And also I have another table which I usally left join with the original table, with about 100000 rows</p>
  
  <p><strong>Erp_In_failed:</strong></p>
  
  <pre><code>corr_id
  interface
  error (clob)   
  input (clob)
  </code></pre>
  
  <p>with index on (corr_id and interface)</p>
  
  <p>The query I want to optimize is Simple as this:</p>
  
  <pre><code>SELECT a.corr_id, a.interface, a.indate, b.error 
  FROM erp_in a left join erp_in_failed b on a.corr_id = b.corr_id and a.interface =          b.interface
  Order by a.indate desc;
  </code></pre>
  
  <p>If I remove the order by, the query isn't taking that long, but ordering the data takes around 3 minutes if not more.</p>
  
  <p>What can I do to optimize the query?
  I was thinking on Partitioning / Removing old data to history table / maybe creating a sequence primary key and order by it or anything else you have in mind...</p>
  
  <p><strong>Edit:</strong><br>
  Execution plan says full table scan, and it's not the join that takes so long it's the order by.<br>
  Even this query takes forever:</p>
  
  <pre><code>SELECT * FROM erp_in
  ORDER BY indate;
  </code></pre>
  
  <p>I have tried using Paging, but that doesn't work also, and takes few minutes for 20 results, maybe I am doing it wrong?</p>
  
  <p>If I add WHERE clause on indate field, it uses the index, but only when it is less than 20 days old, anything other than that still uses full table scan. (even with 40 days, adding INDEX hint made the query run faster, but still not enough).</p>
  
  <p>And just for the curiosity, I have a simple table with 1 million rows, and order by takes few seconds, what's the difference? is 1 million is enough to sort it in RAM?</p>
  
  <p>Thanks,</p>