title: Oracle - Conditional where clause performance issue
body: <p>Considering two tables, joined in such a way : </p>
  
  <pre><code>select *
  from   table_1
  left   join table_2
  on     table_1.lnk_id = table_2.lnk_id
  ;
  </code></pre>
  
  <p>There is an index on "lnk_id" on table_1, and on table_2. This returns 80 Million rows.</p>
  
  <p>I am using conditional where clause, with variables set by my front-end (APEX):</p>
  
  <p>:all        : If=1, should return all rows.</p>
  
  <p>:desired_id : The object I want to return. Can be a null value, in which case I want to return null value rows only.</p>
  
  <p>I coded this first :</p>
  
  <pre><code>select *
  from   table_1
  left   join table_2
  on     table_1.some_id = table_2.some_id
  
  where  (
           case when :all = 1
                then 1
                when :desired_id is null and table_2.desired_id is null
                then 1
                when :desired_id = table_2.desired_id
                then 1
                else 0
                end = 1
         )
  </code></pre>
  
  <p>Given :all = 0 and :desired_id = some non-null value to select the rows the user desires, I experience terrible performance.</p>
  
  <p>I learned I had to avoid "case" in "where" clause, so adapted to : </p>
  
  <pre><code>where (
          :all = 1
          or (:desired_id is null and table_2.desired_id is null)
          or :desired_id = table_2.desired_id
        )
  </code></pre>
  
  <p>No chance, this is as slow as the "case" solution.</p>
  
  <p>I realized this : </p>
  
  <pre><code>where  (:desired_id = table_2.desired_id);
  </code></pre>
  
  <p>--> 0.047s - Super fast</p>
  
  <pre><code>where  (:desired_id = table_2.desired_id or 0 = 1);
  </code></pre>
  
  <p>--> 0.062s - Super fast</p>
  
  <pre><code>where  (:desired_id = table_2.desired_id or :all = 1);
  </code></pre>
  
  <p>--> <strong>235s</strong> - Super slow</p>
  
  <p>So I can definitely find the desired object across my 80 M rows in no time, with the where( ... = ... or ... = 1) construction : the optimizer must take the wrong decision when I use :all.</p>
  
  <p>Would anyone be able to guide ?</p>
  
  <p>I would rather avoid the build-a-dynamic-query solution if possible as it makes it a lot more complex to implement and manage I believe and this really sounds like ... it should work with plain SQL.</p>
  
  <p>-- Edit to add the plans --</p>
  
  <h1>Good plan</h1>
  
  <pre><code>select *
  from   table_2
  left   join table_1
  on     table_2.lnk_id = table_1.lnk_id
  where  (:desired_id = table_1.desired_id or 0 = 1);
  
  
  Plan hash value: 1995399472
  
  --------------------------------------------------------------------------------------------------------------------------
  | Id  | Operation                             | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
  --------------------------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                      |                  |   129 | 42183 |    45   (0)| 00:00:01 |       |       |
  |   1 |  NESTED LOOPS                         |                  |   129 | 42183 |    45   (0)| 00:00:01 |       |       |
  |   2 |   NESTED LOOPS                        |                  |   138 | 42183 |    45   (0)| 00:00:01 |       |       |
  |   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TABLE_1          |     3 |   435 |     7   (0)| 00:00:01 |       |       |
  |*  4 |     INDEX RANGE SCAN                  | TABLE_1_I1       |     3 |       |     3   (0)| 00:00:01 |       |       |
  |*  5 |    INDEX RANGE SCAN                   | TABLE_2_I2       |    46 |       |     3   (0)| 00:00:01 |       |       |
  |   6 |   TABLE ACCESS BY GLOBAL INDEX ROWID  | TABLE_2          |    40 |  7280 |    21   (0)| 00:00:01 | ROWID | ROWID |
  --------------------------------------------------------------------------------------------------------------------------
  
  
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     4 - access("TABLE_1"."DESIRED_ID"=:DESIRED_ID)
     5 - access("TABLE_2"."LNK_ID"="TABLE_1"."LNK_ID")
  </code></pre>
  
  <h1>Wrong plan</h1>
  
  <pre><code>explain plan for
  select *
  from   table_2
  left   join table_1
  on     table_2.lnk_id = table_1.lnk_id
  where    (:desired_id = table_1.desired_id or :p3070100_all = 1);
  
  Plan hash value: 94704160
  
  ----------------------------------------------------------------------------------------------------------------
  | Id  | Operation              | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
  ----------------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT       |               |    79M|    24G|       |  1441K  (1)| 00:00:57 |       |       |
  |*  1 |  FILTER                |               |       |       |       |            |          |       |       |
  |*  2 |   HASH JOIN RIGHT OUTER|               |    79M|    24G|   484M|  1441K  (1)| 00:00:57 |       |       |
  |   3 |    TABLE ACCESS FULL   | TABLE_1       |  3238K|   447M|       | 19152   (1)| 00:00:01 |       |       |
  |   4 |    PARTITION RANGE ALL |               |    79M|    13G|       |   668K  (1)| 00:00:27 |     1 |1048575|
  |   5 |     TABLE ACCESS FULL  | TABLE_2       |    79M|    13G|       |   668K  (1)| 00:00:27 |     1 |1048575|
  ----------------------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     1 - filter("TABLE_1"."DESIRED_ID"=:DESIRED_ID OR TO_NUMBER(:P3070100_ALL)=1)
     2 - access("TABLE_2"."LNK_ID"="TABLE_1"."LNK_ID"(+))   
  </code></pre>
  
  <p>Thank you,
  David</p>