Oracle 23ai

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Source - https://stackoverflow.com/q/80007355
-- Posted by Schrei, modified by community. See post 'Timeline' for change history
-- Retrieved 2026-10-01, License - CC BY-SA 4.0

CREATE OR REPLACE FORCE EDITIONABLE VIEW view_abcd (DUMMY) AS
SELECT 1 as DUMMY
FROM dual a
OUTER APPLY (
    SELECT *
    FROM dual b
    JOIN dual c
    ON c.DUMMY = (
        SELECT d.DUMMY
        FROM dual d
        WHERE d.DUMMY = a.DUMMY
        FETCH FIRST 1 ROW ONLY
     )
);
SELECT 1 AS one_row
FROM dual
WHERE   EXISTS (SELECT * FROM view_abcd)
AND NOT EXISTS (SELECT * FROM view_abcd);

ONE_ROW

select * from dbms_xplan.display_cursor(null, null, format=>'advanced');

PLAN_TABLE_OUTPUT

SQL_ID  2c965ffcuwqw7, child number 0

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

SELECT 1 AS one_row FROM dual WHERE   EXISTS (SELECT * FROM view_abcd)

AND NOT EXISTS (SELECT * FROM view_abcd)

Plan hash value: 3255242695

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

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

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

|   0 | SELECT STATEMENT       |                 |       |       |    18 (100)|          |

|*  1 |  FILTER                |                 |       |       |            |          |

|   2 |   FAST DUAL            |                 |     1 |       |     2   (0)| 00:00:01 |

|   3 |   NESTED LOOPS OUTER   |                 |     1 |     2 |     8   (0)| 00:00:01 |

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

|   5 |    VIEW                | VW_LAT_8D44C30F |     1 |       |     6   (0)| 00:00:01 |

|*  6 |     FILTER             |                 |       |       |            |          |

|   7 |      NESTED LOOPS      |                 |     1 |     2 |     4   (0)| 00:00:01 |

|   8 |       TABLE ACCESS FULL| DUAL            |     1 |     2 |     2   (0)| 00:00:01 |

|   9 |       TABLE ACCESS FULL| DUAL            |     1 |       |     2   (0)| 00:00:01 |

|* 10 |      COUNT STOPKEY     |                 |       |       |            |          |

|* 11 |       TABLE ACCESS FULL| DUAL            |     1 |     2 |     2   (0)| 00:00:01 |

|  12 |   NESTED LOOPS OUTER   |                 |     1 |     2 |     8   (0)| 00:00:01 |

|  13 |    TABLE ACCESS FULL   | DUAL            |     1 |     2 |     2   (0)| 00:00:01 |

|  14 |    VIEW                | VW_LAT_27476BD6 |     1 |       |     6   (0)| 00:00:01 |

|* 15 |     FILTER             |                 |       |       |            |          |

|  16 |      NESTED LOOPS      |                 |     1 |     2 |     4   (0)| 00:00:01 |

|  17 |       TABLE ACCESS FULL| DUAL            |     1 |     2 |     2   (0)| 00:00:01 |

|  18 |       TABLE ACCESS FULL| DUAL            |     1 |       |     2   (0)| 00:00:01 |

|* 19 |      COUNT STOPKEY     |                 |       |       |            |          |

|* 20 |       TABLE ACCESS FULL| DUAL            |     1 |     2 |     2   (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

   1 - SEL$1

   2 - SEL$1        / "DUAL"@"SEL$1"

   3 - SEL$F8026078

   4 - SEL$F8026078 / "A"@"SEL$16"

   5 - SEL$2E13A6DF / "from$_subquery$_021"@"SEL$16"

   6 - SEL$2E13A6DF

   8 - SEL$2E13A6DF / "C"@"SEL$13"

   9 - SEL$2E13A6DF / "B"@"SEL$13"

  10 - SEL$997D63A8

  11 - SEL$997D63A8 / "D"@"SEL$12"

  12 - SEL$DE8D1BB0

  13 - SEL$DE8D1BB0 / "A"@"SEL$8"

  14 - SEL$70BB7B56 / "from$_subquery$_012"@"SEL$8"

  15 - SEL$70BB7B56

  17 - SEL$70BB7B56 / "C"@"SEL$5"

  18 - SEL$70BB7B56 / "B"@"SEL$5"

  19 - SEL$9FBDA764

  20 - SEL$9FBDA764 / "D"@"SEL$4"

Outline Data

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

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('23.1.0')

      DB_VERSION('23.1.0')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$9FBDA764")

      MERGE(@"SEL$4" >"SEL$6")

      OUTLINE_LEAF(@"SEL$70BB7B56")

      MERGE(@"SEL$F23444D6" >"SEL$3")

      OUTLINE_LEAF(@"SEL$DE8D1BB0")

      MERGE(@"SEL$A0D20652" >"SEL$2")

      OUTLINE_LEAF(@"SEL$997D63A8")

      MERGE(@"SEL$12" >"SEL$14")

      OUTLINE_LEAF(@"SEL$2E13A6DF")

      MERGE(@"SEL$403B62F5" >"SEL$11")

      OUTLINE_LEAF(@"SEL$F8026078")

      MERGE(@"SEL$C70E7E7A" >"SEL$10")

      OUTLINE_LEAF(@"SEL$1")

      OUTLINE(@"SEL$6")

      OUTLINE(@"SEL$4")

      OUTLINE(@"SEL$3")

      OUTLINE(@"SEL$F23444D6")

      MERGE(@"SEL$5" >"SEL$7")

      OUTLINE(@"SEL$2")

      OUTLINE(@"SEL$A0D20652")

      MERGE(@"SEL$8" >"SEL$9")

      OUTLINE(@"SEL$14")

      OUTLINE(@"SEL$12")

      OUTLINE(@"SEL$11")

      OUTLINE(@"SEL$403B62F5")

      MERGE(@"SEL$13" >"SEL$15")

      OUTLINE(@"SEL$10")

      OUTLINE(@"SEL$C70E7E7A")

      MERGE(@"SEL$16" >"SEL$17")

      OUTLINE(@"SEL$7")

      OUTLINE(@"SEL$5")

      OUTLINE(@"SEL$9")

      OUTLINE(@"SEL$8")

      OUTLINE(@"SEL$15")

      OUTLINE(@"SEL$13")

      OUTLINE(@"SEL$17")

      OUTLINE(@"SEL$16")

      FULL(@"SEL$F8026078" "A"@"SEL$16")

      NO_ACCESS(@"SEL$F8026078" "from$_subquery$_021"@"SEL$16")

      LEADING(@"SEL$F8026078" "A"@"SEL$16" "from$_subquery$_021"@"SEL$16")

      USE_NL(@"SEL$F8026078" "from$_subquery$_021"@"SEL$16")

      FULL(@"SEL$DE8D1BB0" "A"@"SEL$8")

      NO_ACCESS(@"SEL$DE8D1BB0" "from$_subquery$_012"@"SEL$8")

      LEADING(@"SEL$DE8D1BB0" "A"@"SEL$8" "from$_subquery$_012"@"SEL$8")

      USE_NL(@"SEL$DE8D1BB0" "from$_subquery$_012"@"SEL$8")

      FULL(@"SEL$70BB7B56" "C"@"SEL$5")

      FULL(@"SEL$70BB7B56" "B"@"SEL$5")

      LEADING(@"SEL$70BB7B56" "C"@"SEL$5" "B"@"SEL$5")

      USE_NL(@"SEL$70BB7B56" "B"@"SEL$5")

      PQ_FILTER(@"SEL$70BB7B56" SERIAL)

      FULL(@"SEL$9FBDA764" "D"@"SEL$4")

      FULL(@"SEL$2E13A6DF" "C"@"SEL$13")

      FULL(@"SEL$2E13A6DF" "B"@"SEL$13")

      LEADING(@"SEL$2E13A6DF" "C"@"SEL$13" "B"@"SEL$13")

      USE_NL(@"SEL$2E13A6DF" "B"@"SEL$13")

      PQ_FILTER(@"SEL$2E13A6DF" SERIAL)

      FULL(@"SEL$997D63A8" "D"@"SEL$12")

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

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

   1 - filter(( IS NULL AND  IS NOT NULL))

   6 - filter("C"."DUMMY"=)

  10 - filter(ROWNUM<=1)

  11 - filter("D"."DUMMY"=:B1)

  15 - filter("C"."DUMMY"=)

  19 - filter(ROWNUM<=1)

  20 - filter("D"."DUMMY"=:B1)

Column Projection Information (identified by operation id):

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

   4 - "A"."DUMMY"[VARCHAR2,1]

   7 - "C"."DUMMY"[VARCHAR2,1]

   8 - "C"."DUMMY"[VARCHAR2,1]

  10 - "D"."DUMMY"[VARCHAR2,1]

  11 - "D"."DUMMY"[VARCHAR2,1]

  13 - "A"."DUMMY"[VARCHAR2,1]

  16 - "C"."DUMMY"[VARCHAR2,1]

  17 - "C"."DUMMY"[VARCHAR2,1]

  19 - "D"."DUMMY"[VARCHAR2,1]

  20 - "D"."DUMMY"[VARCHAR2,1]

Query Block Registry:

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

  SEL$1 (PARSER) [FINAL]

  SEL$10 (PARSER)

    SEL$F8026078 (VIEW MERGE SEL$10 ; SEL$C70E7E7A) [FINAL]

  SEL$11 (PARSER)

    SEL$2E13A6DF (VIEW MERGE SEL$11 ; SEL$403B62F5) [FINAL]

  SEL$12 (PARSER)

  SEL$13 (PARSER)

  SEL$14 (PARSER)

    SEL$997D63A8 (VIEW MERGE SEL$14 ; SEL$12) [FINAL]

  SEL$15 (PARSER)

    SEL$403B62F5 (VIEW MERGE SEL$15 ; SEL$13)

  SEL$16 (PARSER)

  SEL$17 (PARSER)

    SEL$C70E7E7A (VIEW MERGE SEL$17 ; SEL$16)

  SEL$2 (PARSER)

    SEL$DE8D1BB0 (VIEW MERGE SEL$2 ; SEL$A0D20652) [FINAL]

  SEL$3 (PARSER)

    SEL$70BB7B56 (VIEW MERGE SEL$3 ; SEL$F23444D6) [FINAL]

  SEL$4 (PARSER)

  SEL$5 (PARSER)

  SEL$6 (PARSER)

    SEL$9FBDA764 (VIEW MERGE SEL$6 ; SEL$4) [FINAL]

  SEL$7 (PARSER)

    SEL$F23444D6 (VIEW MERGE SEL$7 ; SEL$5)

  SEL$8 (PARSER)

  SEL$9 (PARSER)

    SEL$A0D20652 (VIEW MERGE SEL$9 ; SEL$8)

ALTER SESSION SET "_fix_control" = '35915968:0';
SELECT 1 AS one_row
FROM dual
WHERE   EXISTS (SELECT * FROM view_abcd)
AND NOT EXISTS (SELECT * FROM view_abcd);

ONE_ROW

1

select * from dbms_xplan.display_cursor(null,null,format=>'advanced');

PLAN_TABLE_OUTPUT

SQL_ID  2c965ffcuwqw7, child number 2

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

SELECT 1 AS one_row FROM dual WHERE   EXISTS (SELECT * FROM view_abcd)

AND NOT EXISTS (SELECT * FROM view_abcd)

Plan hash value: 1388734953

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

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

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

|   0 | SELECT STATEMENT |      |       |     2 (100)|          |

|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

   1 - SEL$1 / "DUAL"@"SEL$1"

Outline Data

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

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('23.1.0')

      DB_VERSION('23.1.0')

      OPT_PARAM('_fix_control' '35915968:0')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$4")

      OUTLINE_LEAF(@"SEL$6")

      OUTLINE_LEAF(@"SEL$F23444D6")

      MERGE(@"SEL$5" >"SEL$7")

      OUTLINE_LEAF(@"SEL$3")

      OUTLINE_LEAF(@"SEL$DE8D1BB0")

      MERGE(@"SEL$A0D20652" >"SEL$2")

      OUTLINE_LEAF(@"SEL$12")

      OUTLINE_LEAF(@"SEL$14")

      OUTLINE_LEAF(@"SEL$403B62F5")

      MERGE(@"SEL$13" >"SEL$15")

      OUTLINE_LEAF(@"SEL$11")

      OUTLINE_LEAF(@"SEL$F8026078")

      MERGE(@"SEL$C70E7E7A" >"SEL$10")

      OUTLINE_LEAF(@"SEL$1")

      OUTLINE(@"SEL$7")

      OUTLINE(@"SEL$5")

      OUTLINE(@"SEL$2")

      OUTLINE(@"SEL$A0D20652")

      MERGE(@"SEL$8" >"SEL$9")

      OUTLINE(@"SEL$15")

      OUTLINE(@"SEL$13")

      OUTLINE(@"SEL$10")

      OUTLINE(@"SEL$C70E7E7A")

      MERGE(@"SEL$16" >"SEL$17")

      OUTLINE(@"SEL$9")

      OUTLINE(@"SEL$8")

      OUTLINE(@"SEL$17")

      OUTLINE(@"SEL$16")

      FULL(@"SEL$F8026078" "A"@"SEL$16")

      NO_ACCESS(@"SEL$F8026078" "from$_subquery$_021"@"SEL$16")

      LEADING(@"SEL$F8026078" "A"@"SEL$16" "from$_subquery$_021"@"SEL$16")

      USE_NL(@"SEL$F8026078" "from$_subquery$_021"@"SEL$16")

      FULL(@"SEL$DE8D1BB0" "A"@"SEL$8")

      NO_ACCESS(@"SEL$DE8D1BB0" "from$_subquery$_012"@"SEL$8")

      LEADING(@"SEL$DE8D1BB0" "A"@"SEL$8" "from$_subquery$_012"@"SEL$8")

      USE_NL(@"SEL$DE8D1BB0" "from$_subquery$_012"@"SEL$8")

      NO_ACCESS(@"SEL$3" "from$_subquery$_005"@"SEL$3")

      FULL(@"SEL$F23444D6" "C"@"SEL$5")

      FULL(@"SEL$F23444D6" "B"@"SEL$5")

      LEADING(@"SEL$F23444D6" "C"@"SEL$5" "B"@"SEL$5")

      USE_NL(@"SEL$F23444D6" "B"@"SEL$5")

      PQ_FILTER(@"SEL$F23444D6" SERIAL)

      NO_ACCESS(@"SEL$6" "from$_subquery$_010"@"SEL$6")

      FULL(@"SEL$4" "D"@"SEL$4")

      NO_ACCESS(@"SEL$11" "from$_subquery$_014"@"SEL$11")

      FULL(@"SEL$403B62F5" "C"@"SEL$13")

      FULL(@"SEL$403B62F5" "B"@"SEL$13")

      LEADING(@"SEL$403B62F5" "C"@"SEL$13" "B"@"SEL$13")

      USE_NL(@"SEL$403B62F5" "B"@"SEL$13")

      PQ_FILTER(@"SEL$403B62F5" SERIAL)

      NO_ACCESS(@"SEL$14" "from$_subquery$_019"@"SEL$14")

      FULL(@"SEL$12" "D"@"SEL$12")

      END_OUTLINE_DATA

  */

Query Block Registry:

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

  SEL$1 (PARSER) [FINAL]

  SEL$10 (PARSER)

    SEL$F8026078 (VIEW MERGE SEL$10 ; SEL$C70E7E7A) [FINAL]

  SEL$11 (PARSER) [FINAL]

  SEL$12 (PARSER) [FINAL]

  SEL$13 (PARSER)

  SEL$14 (PARSER) [FINAL]

  SEL$15 (PARSER)

    SEL$403B62F5 (VIEW MERGE SEL$15 ; SEL$13) [FINAL]

  SEL$16 (PARSER)

  SEL$17 (PARSER)

    SEL$C70E7E7A (VIEW MERGE SEL$17 ; SEL$16)

  SEL$2 (PARSER)

    SEL$DE8D1BB0 (VIEW MERGE SEL$2 ; SEL$A0D20652) [FINAL]

  SEL$3 (PARSER) [FINAL]

  SEL$4 (PARSER) [FINAL]

  SEL$5 (PARSER)

  SEL$6 (PARSER) [FINAL]

  SEL$7 (PARSER)

    SEL$F23444D6 (VIEW MERGE SEL$7 ; SEL$5) [FINAL]

  SEL$8 (PARSER)

  SEL$9 (PARSER)

    SEL$A0D20652 (VIEW MERGE SEL$9 ; SEL$8)

select * from v$version;

BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.9.0.25.07
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
0