Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TEMP_TEST (
    CODE NUMBER,
    DATE_COL NUMBER,
    VAL NUMBER
);
BEGIN
INSERT INTO TEMP_TEST (code, DATE_COL, val) values (1, 20210101, 1);
INSERT INTO TEMP_TEST (code, DATE_COL, val) values (1, 20210102, 1);
INSERT INTO TEMP_TEST (code, DATE_COL, val) values (1, 20210103, 1);
INSERT INTO TEMP_TEST (code, DATE_COL, val) values (2, 20210101, 1);
END;
/
1 rows affected

SELECT t.code,
       d.date_col,
       COALESCE(t.val, 1) AS val
FROM   (
         SELECT DISTINCT date_col FROM temp_test
       ) d
       LEFT OUTER JOIN temp_test t
       PARTITION BY (t.code)
       ON (t.date_col = d.date_col);


CODE
DATE_COL
VAL


1
20210101
1

1
20210102
1

1
20210103
1

2
20210101
1

2
20210102
1

2
20210103
1


EXPLAIN PLAN FOR
SELECT t.code,
       d.date_col,
       COALESCE(t.val, 1) AS val
FROM   (
         SELECT DISTINCT date_col FROM temp_test
       ) d
       LEFT OUTER JOIN temp_test t
       PARTITION BY (t.code)
       ON (t.date_col = d.date_col);
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());


PLAN_TABLE_OUTPUT


Plan hash value: 500563185

 

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

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

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

|   0 | SELECT STATEMENT            |           |     4 |   156 |    10  (30)| 00:00:01 |

|   1 |  VIEW                       |           |     4 |   156 |    10  (30)| 00:00:01 |

|   2 |   MERGE JOIN PARTITION OUTER|           |     4 |   208 |    10  (30)| 00:00:01 |

|   3 |    SORT JOIN                |           |     4 |    52 |     5  (40)| 00:00:01 |

|   4 |     VIEW                    |           |     4 |    52 |     4  (25)| 00:00:01 |

|   5 |      HASH UNIQUE            |           |     4 |    52 |     4  (25)| 00:00:01 |

|   6 |       TABLE ACCESS FULL     | TEMP_TEST |     4 |    52 |     3   (0)| 00:00:01 |

|*  7 |    SORT PARTITION JOIN      |           |     4 |   156 |     4  (25)| 00:00:01 |

|   8 |     TABLE ACCESS FULL       | TEMP_TEST |     4 |   156 |     3   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   7 - access("T"."DATE_COL"="D"."DATE_COL")

       filter("T"."DATE_COL"="D"."DATE_COL")

 

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)


select distinct a.code, b.date_col, c.val
from temp_test a cross join temp_test b cross join temp_test c


CODE
DATE_COL
VAL


2
20210102
1

2
20210103
1

1
20210101
1

2
20210101
1

1
20210102
1

1
20210103
1


EXPLAIN PLAN FOR
select distinct a.code, b.date_col, c.val
from temp_test a cross join temp_test b cross join temp_test c
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());


PLAN_TABLE_OUTPUT


Plan hash value: 1208406217

 

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

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

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

|   0 | SELECT STATEMENT       |           |    64 |  2496 |    34   (3)| 00:00:01 |

|   1 |  HASH UNIQUE           |           |    64 |  2496 |    34   (3)| 00:00:01 |

|   2 |   MERGE JOIN CARTESIAN |           |    64 |  2496 |    33   (0)| 00:00:01 |

|   3 |    MERGE JOIN CARTESIAN|           |    16 |   416 |    10   (0)| 00:00:01 |

|   4 |     TABLE ACCESS FULL  | TEMP_TEST |     4 |    52 |     3   (0)| 00:00:01 |

|   5 |     BUFFER SORT        |           |     4 |    52 |     7   (0)| 00:00:01 |

|   6 |      TABLE ACCESS FULL | TEMP_TEST |     4 |    52 |     2   (0)| 00:00:01 |

|   7 |    BUFFER SORT         |           |     4 |    52 |    31   (0)| 00:00:01 |

|   8 |     TABLE ACCESS FULL  | TEMP_TEST |     4 |    52 |     1   (0)| 00:00:01 |

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

 

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)
CODE	DATE_COL	VAL
1	20210101	1
1	20210102	1
1	20210103	1
2	20210101	1
2	20210102	1
2	20210103	1