Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
  CREATE TABLE TEST_DATA 
   (ID NUMBER(11,0)  PRIMARY KEY, 
    FORMATTED_RESULT VARCHAR2(255 BYTE), 
    F_RESULT NUMBER, 
    IDNUM NUMBER(11,0), 
    IDNUM_DESCRIPTION VARCHAR2(128 BYTE), 
    LAB_NUMBER NUMBER(11,0), 
    SEQ_NUMBER NUMBER(11,0),
    ORDERNO NUMBER(11,0),
    SUPPL_FORMATTED_RESULT VARCHAR2(255 BYTE), 
    SUPPL_IDNUM NUMBER(11,0), 
    SUPPL_IDNUM_DESCRIPTION VARCHAR2(128 BYTE), 
    SUPPL_UNIT VARCHAR2(16 BYTE)
   ) ;
 CREATE INDEX TD_CUFR_CIDN_SN_LN ON TEST_DATA (UPPER(COALESCE(SUPPL_FORMATTED_RESULT,FORMATTED_RESULT)), COALESCE(SUPPL_IDNUM,IDNUM), SEQ_NUMBER, LAB_NUMBER) ;
CREATE TABLE REQUEST_INFO 
   (ID NUMBER(11,0) PRIMARY KEY, 
    CHARGE_CODE VARCHAR2(32 BYTE), 
    LAB_NUMBER NUMBER(11,0), 
    SEQ_NUMBER NUMBER(11,0)
   ) ;
CREATE INDEX RI_SN_LN ON REQUEST_INFO (SEQ_NUMBER, LAB_NUMBER) ;
EXPLAIN PLAN FOR 
select TD.LAB_NUMBER 
from REQUEST_INFO RI 
LEFT JOIN TEST_DATA TD ON  TD.LAB_NUMBER = RI.LAB_NUMBER AND TD.SEQ_NUMBER = RI.SEQ_NUMBER 
where UPPER(COALESCE(TD.SUPPL_FORMATTED_RESULT,TD.FORMATTED_RESULT))='491(10)376'
  and COALESCE(TD.SUPPL_IDNUM, TD.IDNUM)=40549;
SELECT * FROM table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT

Plan hash value: 2450448547

------------------------------------------------------------------------------------

| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |              |     1 |   336 |     5  (20)| 00:00:01 |

|*  1 |  FILTER             |              |       |       |            |          |

|*  2 |   HASH JOIN OUTER   |              |     1 |   336 |     5  (20)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| REQUEST_INFO |     1 |    26 |     2   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL| TEST_DATA    |     1 |   310 |     2   (0)| 00:00:01 |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(UPPER(COALESCE("TD"."SUPPL_FORMATTED_RESULT","TD"."FORMATTED_R

              ESULT"))='491(10)376' AND COALESCE("TD"."SUPPL_IDNUM","TD"."IDNUM")=40549)

   2 - access("TD"."SEQ_NUMBER"(+)="RI"."SEQ_NUMBER" AND

              "TD"."LAB_NUMBER"(+)="RI"."LAB_NUMBER")

Note

-----

   - dynamic sampling used for this statement (level=2)

EXPLAIN PLAN FOR select TD.LAB_NUMBER 
from REQUEST_INFO RI 
LEFT JOIN TEST_DATA TD ON  TD.LAB_NUMBER = RI.LAB_NUMBER AND TD.SEQ_NUMBER = RI.SEQ_NUMBER 
where UPPER(COALESCE(TD.SUPPL_FORMATTED_RESULT,TD.FORMATTED_RESULT))='491(10)376'
  and COALESCE(TD.SUPPL_IDNUM, TD.IDNUM)=40549 
and TD.LAB_NUMBER IS NOT NULL
;
SELECT * FROM table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT

Plan hash value: 1684531989

----------------------------------------------------------------------------------------

| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                    |     1 |   194 |     1   (0)| 00:00:01 |

|   1 |  NESTED LOOPS     |                    |     1 |   194 |     1   (0)| 00:00:01 |

|   2 |   INDEX FULL SCAN | RI_SN_LN           |     1 |    26 |     1   (0)| 00:00:01 |

|*  3 |   INDEX RANGE SCAN| TD_CUFR_CIDN_SN_LN |     1 |   168 |     0   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access(UPPER(COALESCE("SUPPL_FORMATTED_RESULT","FORMATTED_RESULT"))='491(

              10)376' AND COALESCE("SUPPL_IDNUM","IDNUM")=40549 AND

              "TD"."SEQ_NUMBER"="RI"."SEQ_NUMBER" AND "TD"."LAB_NUMBER"="RI"."LAB_NUMBER")

       filter("TD"."LAB_NUMBER" IS NOT NULL)

Note

-----

   - dynamic sampling used for this statement (level=2)

EXPLAIN PLAN FOR select TD.LAB_NUMBER 
from REQUEST_INFO RI 
LEFT JOIN TEST_DATA TD ON  TD.LAB_NUMBER = RI.LAB_NUMBER AND TD.SEQ_NUMBER = RI.SEQ_NUMBER 
where UPPER(COALESCE(TD.SUPPL_FORMATTED_RESULT,TD.FORMATTED_RESULT))='491(10)376'
  and COALESCE(TD.SUPPL_IDNUM, TD.IDNUM)=40549 
and (TD.ID IS NOT NULL) -- switches to INNER JOIN
;
SELECT * FROM table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT

Plan hash value: 865752842

----------------------------------------------------------------------------------------

| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                    |     1 |   207 |     0   (0)| 00:00:01 |

|   1 |  NESTED LOOPS     |                    |     1 |   207 |     0   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN| TD_CUFR_CIDN_SN_LN |     1 |       |     0   (0)| 00:00:01 |

|*  3 |   INDEX RANGE SCAN| RI_SN_LN           |     1 |    26 |     0   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access(UPPER(COALESCE("SUPPL_FORMATTED_RESULT","FORMATTED_RESULT"))='491(

              10)376' AND COALESCE("SUPPL_IDNUM","IDNUM")=40549)

   3 - access("TD"."SEQ_NUMBER"="RI"."SEQ_NUMBER" AND

              "TD"."LAB_NUMBER"="RI"."LAB_NUMBER")

Note

-----

   - dynamic sampling used for this statement (level=2)

EXPLAIN PLAN FOR select TD.LAB_NUMBER 
from REQUEST_INFO RI 
LEFT JOIN TEST_DATA TD ON  TD.LAB_NUMBER = RI.LAB_NUMBER AND TD.SEQ_NUMBER = RI.SEQ_NUMBER 
where UPPER(COALESCE(TD.SUPPL_FORMATTED_RESULT,TD.FORMATTED_RESULT))='491(10)376'
  and COALESCE(TD.SUPPL_IDNUM, TD.IDNUM)=40549 
and (TD.LAB_NUMBER IS NOT NULL OR TD.SEQ_NUMBER IS NOT NULL) -- does not switch to INNER JOIN
;
SELECT * FROM table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT

Plan hash value: 2450448547

------------------------------------------------------------------------------------

| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |              |     1 |   336 |     5  (20)| 00:00:01 |

|*  1 |  FILTER             |              |       |       |            |          |

|*  2 |   HASH JOIN OUTER   |              |     1 |   336 |     5  (20)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| REQUEST_INFO |     1 |    26 |     2   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL| TEST_DATA    |     1 |   310 |     2   (0)| 00:00:01 |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(UPPER(COALESCE("TD"."SUPPL_FORMATTED_RESULT","TD"."FORMATTED_R

              ESULT"))='491(10)376' AND COALESCE("TD"."SUPPL_IDNUM","TD"."IDNUM")=40549

              AND ("TD"."LAB_NUMBER" IS NOT NULL OR "TD"."SEQ_NUMBER" IS NOT NULL))

   2 - access("TD"."SEQ_NUMBER"(+)="RI"."SEQ_NUMBER" AND

              "TD"."LAB_NUMBER"(+)="RI"."LAB_NUMBER")

Note

-----

   - dynamic sampling used for this statement (level=2)