title: Retrieving a large dataset from a very large table
body: <p>I have a very large table in oracle that contains 140+ million rows. Currently we are doing three full table scans on this table nightly, and using some of the results to populate a tmp table. That tmp table is then turned into a very large report (usually 140K + lines).</p>
  
  <p>The big table is called tasklog and has the following structure has:
  tasklog_id  (number)  - PK
  document_id (number)
  date_time_in   (date)
  + a few more rows that aren't relevant</p>
  
  <p>There are millions of different document ids each repeated between 1 and several hundred times, date_time_in is the time this entry was put into the database.</p>
  
  <p>All of the full table scans looks like this</p>
  
  <pre><code>DECLARE
  n_prevdocid     number;
  
  cursor tasks is
     select * 
     from tasklog
     order by document_id, date_time_in DESC;
  
  BEGIN
  
  for tk in tasks
  loop
      if n_prevdocid &lt;&gt; tk.document_id then
           -- *code snipped*
  
      end if;
      n_prevdocid = tk.document_id;
  end loop;
  
  END;
  /
  </code></pre>
  
  <p>So my question: is there a quick (ish) way to get a distinct list of document_ids with the row having the most recent date_time_in. This could dramatically speed up the whole thing. Or can anyone think of a better way of retrieving this data daily?</p>
  
  <p>Things that may be relevant, this table only ever has rows inserted with current date time. It is not range paritioned but I can't see how that might help me. No rows are ever updated or deleted. There are about 70k - 80k rows inserted daily.</p>