title: SQL: how do I speed up this query
body: <p>Here is the situation.  I have one table that contains records based on records in many different tables (t1 below).  t2 is one of the tables that has information pulled from it into t1.  </p>
  
  <pre><code>t1
    table_oid --which table id is a FK to
    id        --fk to other table
    store_num --field
  
  t2
    t2_id
  </code></pre>
  
  <p>Here is what I need to find: I need the largest t2_id where the store_num is not null in the corresponding record of t1.  Here is the query I wrote:</p>
  
  <pre><code>select max(id) from t1
  join t2 on t2.t2_id = t1.id
  where store_num is not null
  and table_oid = 1234;
  </code></pre>
  
  <p>However, this takes fairly long.  I think this should be a fast query.  all _ids have indexes for them. (t1.id/t1.table_oid, t2.t2_id).  The vast majority of entries in t1 have a store_num.  </p>
  
  <p>Mentally, I would get the t2_ids in desc order, than one by one, try them against t1 until I found the first one that had a store_num.  </p>
  
  <pre><code>select t2_id from t2 order by t2_id desc;
  </code></pre>
  
  <p>has an explain cost of 25612</p>
  
  <pre><code>select t1.* from t1 where table_oid = 1234
  and id in (select max(t2_id) from t2);
  </code></pre>
  
  <p>has an explain cost of 8.</p>
  
  <p>So why wouldn't the above query be a cost of at most 25612*8 = 204896?   When I explain it, it comes back as more than 3 times that.  </p>
  
  <p>Really, my question is how do I re-write that query to run faster.</p>
  
  <p>NOTE:  I am using Oracle. </p>
  
  <p>EDIT:</p>
  
  <p>t2 has 11,895,731 rows<br>
  t1 has 473,235,192 rows</p>
  
  <p>EDIT 2:</p>
  
  <p>As I've tried different things, the part of the query that is taking the longest is the full scan on t1 looking for the store_num.  Is there a way to keep this from doing a full scan, since I only need the biggest entry?</p>