title: Oracle. Select all if parameter is null else return specific item issue
body: <p>I need to return all records if input parameter is null.</p>
  
  <p>I've written a simple query </p>
  
  <pre><code>declare  
     l_sql varchar2(100);
     i number := 1;
   begin
     l_sql:= 'EXPLAIN PLAN for select * from job where :i is null or id = :i';
     execute immediate l_sql;
     commit;
    end;
  
  SELECT * FROM TABLE(DBMS_XPLAN.display); 
  
  | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                                                                                                                                  
  |   0 | SELECT STATEMENT   |      |  1386 |   217K|   226   (1)| 00:00:03 |       |       |                                                                                                                                                                                                                  
  |   1 |  PARTITION LIST ALL|      |  1386 |   217K|   226   (1)| 00:00:03 |     1 |    13 |                                                                                                                                                                                                                  
  |*  2 |   TABLE ACCESS FULL| JOB  |  1386 |   217K|   226   (1)| 00:00:03 |     1 |    13 |                                                                                                                                                                                                                  
  </code></pre>
  
  <p>As you can see index is not working, but if I remove ":i is null" check index starts to work.</p>
  
  <pre><code>declare  
     l_sql varchar2(100);
     i number := 1;
   begin
     l_sql:= 'EXPLAIN PLAN for select * from job where id = :i';
     execute immediate l_sql;
     commit;
    end;
  
  SELECT * FROM TABLE(DBMS_XPLAN.display);
  
  | Id  | Operation                          | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                                                                                                                                                             
  
  |   0 | SELECT STATEMENT                   |           |     1 |   161 |     2   (0)| 00:00:01 |       |       |                                                                                                                                                                                             
  |   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| JOB       |     1 |   161 |     2   (0)| 00:00:01 | ROWID | ROWID |                                                                                                                                                                                             
  |*  2 |   INDEX RANGE SCAN                 | IX_JOB_ID |     1 |       |     1   (0)| 00:00:01 |       |       |
  </code></pre>
  
  <p>So what is the better way to complete this task? Why index is not work in that situation?</p>
  
  <p>Keep in mind that procedure can have many such parameters.</p>