clear markdown compare help best fiddles feedback dbanow.uk
≡
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2555311 fiddles created (37426 in the last week).
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) ) ;
✓
 hidden batch(es)
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) ;
✓
 hidden batch(es)
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) ) ;
✓
 hidden batch(es)
CREATE INDEX RI_SN_LN ON REQUEST_INFO (SEQ_NUMBER, LAB_NUMBER) ;
✓
 hidden batch(es)
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;
✓
 hidden batch(es)
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)
…
 hidden batch(es)
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 ;
✓
 hidden batch(es)
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)
…
 hidden batch(es)
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 ;
✓
 hidden batch(es)
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)
…
 hidden batch(es)
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 ;
✓
 hidden batch(es)
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)
…
 hidden batch(es)