title: OR Condition making the query execution slow
body: <p>I am trying to execute a query (in Oracle SQL) with a <code>WHERE</code> clause in it as follows:</p>
  
  <pre><code>    SELECT booking_line_id
  FROM booking_line
  WHERE    (booking_line_id in
    (SELECT sbli.booking_line_id
     FROM search_booking_line_index sbli,
       policy_record pr,
       policy_record_revision prr
     WHERE sbli.attribute_name = 2209
     AND sbli.policy_record_id = pr.policy_record_id
     AND pr.latest_revision_id = prr.policy_record_revision_id
     AND LOWER(prr.broker_reference) LIKE '%123%'));
  </code></pre>
  
  <p>Execution plan: </p>
  
  <pre><code>|   0 | SELECT STATEMENT       |                        | 19027 |   334K|   455   (3)| 00:00:03 |
  |   1 |  NESTED LOOPS          |                        | 19027 |   334K|   455   (3)| 00:00:03 |
  |   2 |   VIEW                 | VW_NSO_1               | 19027 |   241K|   449   (2)| 00:00:03 |
  |   3 |    HASH UNIQUE         |                        | 19027 |   798K|            |          |
  |*  4 |     HASH JOIN          |                        | 19027 |   798K|   449   (2)| 00:00:03 |
  |*  5 |      HASH JOIN         |                        |   464 | 13456 |    31   (4)| 00:00:01 |
  |*  6 |       TABLE ACCESS FULL| POLICY_RECORD_REVISION |   464 |  9280 |    21   (0)| 00:00:01 |
  |*  7 |       TABLE ACCESS FULL| POLICY_RECORD          |  4067 | 36603 |     9   (0)| 00:00:01 |
  |*  8 |      INDEX RANGE SCAN  | SBLI_ATTRIBUTE_CDX     |   166K|  2282K|   416   (1)| 00:00:03 |
  |*  9 |   INDEX UNIQUE SCAN    | BOOKING_LINE_PK        |     1 |     5 |     1   (0)| 00:00:01 |
  </code></pre>
  
  <p>This query executes very fast and returns rows within no time. But if I add a <code>OR</code> condition as below, the query hangs (or is taking a very long time) :</p>
  
  <pre><code>    SELECT booking_line_id
  FROM booking_line
  WHERE 1=0 or   (booking_line_id in
    (SELECT sbli.booking_line_id
     FROM search_booking_line_index sbli,
       policy_record pr,
       policy_record_revision prr
     WHERE sbli.attribute_name = 2209
     AND sbli.policy_record_id = pr.policy_record_id
     AND pr.latest_revision_id = prr.policy_record_revision_id
     AND LOWER(prr.broker_reference) LIKE '%123%'));
  </code></pre>
  
  <p>Execution plan:</p>
  
  <pre><code>|   0 | SELECT STATEMENT               |                           |   166K|   811K|   484   (2)| 00:00:03 |
  |*  1 |  FILTER                        |                           |       |       |            |          |
  |   2 |   INDEX FAST FULL SCAN         | BOOKING_LINE_PK           |   166K|   811K|    66   (5)| 00:00:01 |
  |   3 |   NESTED LOOPS                 |                           |     1 |    43 |   419   (1)| 00:00:03 |
  |   4 |    NESTED LOOPS                |                           |     1 |    23 |   418   (1)| 00:00:03 |
  |*  5 |     INDEX RANGE SCAN           | SBLI_ATTRIBUTE_CDX        |     1 |    14 |   417   (1)| 00:00:03 |
  |*  6 |     TABLE ACCESS BY INDEX ROWID| POLICY_RECORD             |     1 |     9 |     1   (0)| 00:00:01 |
  |*  7 |      INDEX UNIQUE SCAN         | POLICY_RECORD_PK          |     1 |       |     1   (0)| 00:00:01 |
  |*  8 |    TABLE ACCESS BY INDEX ROWID | POLICY_RECORD_REVISION    |     1 |    20 |     1   (0)| 00:00:01 |
  |*  9 |     INDEX UNIQUE SCAN          | POLICY_RECORD_REVISION_PK |     1 |       |     1   (0)| 00:00:01 |
  </code></pre>
  
  <p>Expected that this should take the same time as the first query. But surprisingly, it didn't.</p>
  
  <p>Can anyone please help me with the reason for the same? </p>