title: Oracle SQL efficient query on Partitioned Table: "order by case"
body: <p>I get a table of about 100 billions records partitioned on the key <code>date_insert</code>.</p>
  
  <p><strong>MY_BIG_TABLE</strong></p>
  
  <ul>
  <li>id NUMERIC secondary index</li>
  <li>date_insert DATE primary index</li>
  <li>date_fact DATE secondary index</li>
  <li>data BLOB</li>
  </ul>
  
  <p>Input:</p>
  
  <ul>
  <li>:date_input1 </li>
  <li>:date_input2 </li>
  <li>:date_input3 </li>
  <li>:id</li>
  </ul>
  
  <p>Note: <code>:date_input1 &lt; :date_input2</code></p>
  
  <p>I want</p>
  
  <ol>
  <li><code>IF date_fact &lt; :date_input_1</code> the first row ordered by date_fact <strong>DESC</strong></li>
  <li><code>ELSE IF date_fact &lt; :date_input_2</code> the first row ordered by date_fact <strong>ASC</strong></li>
  <li><code>ELSE</code> the latest </li>
  </ol>
  
  <p>A query prototype should be:</p>
  
  <pre><code>SELECT date_insert, date_fact, data (
  
          SELECT
              date_insert, 
              date_fact,
              CASE 
                 WHEN (date_fact &lt; :date_input_1 AND id= :id) : 2
                 WHEN (date_fact &lt; :date_input_2 AND id= :id) : 1
                 ELSE : 0  
              check,
  
              CASE 
                 WHEN (date_fact &lt; :date_input_1 AND id= :id) : NULL
                 WHEN (date_fact &lt; :date_input_2 AND id= :id) : data
                 ELSE : NULL  
              data
  
          FROM MY_BIG_TABLE
          WHERE date_insert &gt; :date_input3   
          order by check, DECODE(check, 1, date_fact) ASC,
                          date_fact DESC 
  
  ) WHERE ROWNUM &lt;2
  </code></pre>
  
  <p>I need to do this hard work to avoid too many access to this table.  <strong>Any suggetsion to rebuild completely the query to be simpler and easier?</strong></p>