LEAD and LAG taking hours on a very large table!

You posted populate TB_SOURCE_COPY_TRIMMED  table took ~ 17 mins. How long does it take to:

SET TIMING ON
BEGIN
    FOR V_REC IN (SELECT * FROM TB_SOURCE_COPY_TRIMMED ORDER BY ULIMORE,VISIT_DATE_DT) LOOP
      NULL;
    END LOOP;
END;
/

SY.

Any chance you are on 11G? There was a bug which was fixed in 12C where LAG/LEAD with IGNORE NULLS was extremely slow:

SQL> CREATE TABLE a AS SELECT LEVEL ID ,decode(mod(LEVEL,10),1,'CODE'||LEVEL) CODE from dual CONNECT BY LEVEL<20001;

Table created.

SQL> CREATE TABLE TEST AS
  2  SELECT ID,
  3  LAG(CODE ignore nulls) OVER(ORDER BY ID ) CODE
  4  FROM a;

Table created.

Elapsed: 00:00:44.36
SQL> drop table test purge;

Table dropped.

Elapsed: 00:00:00.01
SQL> CREATE TABLE TEST AS
  2  SELECT ID,
  3  LAG(CODE) OVER(ORDER BY ID ) CODE
  4  FROM a;

Table created.

Elapsed: 00:00:00.06
SQL>

If so, use LAST_VALUE/FIRST_VALUE with IGNORE NULLS.

SY.

Ah, I thought it was fixed but it wasn't and in 19C it is even worse than in 12C.

12C:

SQL> select version from v$instance;

VERSION
-----------------
12.2.0.1.0

Elapsed: 00:00:00.01
SQL> CREATE TABLE a AS SELECT LEVEL ID ,decode(mod(LEVEL,10),1,'CODE'||LEVEL) CODE from dual CONNECT BY LEVEL<20001;

Table created.

Elapsed: 00:00:00.16
SQL> CREATE TABLE TEST_LAG AS
  2      SELECT ID,
  3      LAG(CODE ignore nulls) OVER(ORDER BY ID ) CODE
  4      FROM a;

Table created.

Elapsed: 00:00:23.04
SQL> CREATE TABLE TEST_LAST_VALUE AS
  2      SELECT ID,
  3      LAST_VALUE(CODE ignore nulls) OVER(ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) CODE
  4      FROM a;

Table created.

Elapsed: 00:00:00.04
SQL> SELECT * FROM TEST_LAG
  2  MINUS
  3  SELECT * FROM TEST_LAST_VALUE;

no rows selected

Elapsed: 00:00:00.01
SQL>

And 19C:

SQL> select version_full from v$instance;

VERSION_FULL
-----------------
19.13.0.0.0

Elapsed: 00:00:00.05
SQL> CREATE TABLE a AS SELECT LEVEL ID ,decode(mod(LEVEL,10),1,'CODE'||LEVEL) CODE from dual CONNECT BY LEVEL<20001;

Table created.

Elapsed: 00:00:00.16
SQL> CREATE TABLE TEST_LAG AS
  2      SELECT ID,
  3      LAG(CODE ignore nulls) OVER(ORDER BY ID ) CODE
  4      FROM a;

Table created.

Elapsed: 00:00:39.20
SQL> CREATE TABLE TEST_LAST_VALUE AS
  2      SELECT ID,
  3      LAST_VALUE(CODE ignore nulls) OVER(ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) CODE
  4      FROM a;

Table created.

Elapsed: 00:00:00.09
SQL> SELECT * FROM TEST_LAG
  2  MINUS
  3  SELECT * FROM TEST_LAST_VALUE;

no rows selected

Elapsed: 00:00:00.04
SQL>

Anyway, use LAST_VALUE/FIRST VALUE IGNORE NULLS instead. And, if you want to, open SR with Oracle Support.

SY.

LAG(p.ED_UCC_DT IGNORE NULLS) OVER(PARTITION BY p.ULIMORE ORDER BY p.VISIT_DATE_DT)

Is same as

LAST_VALUE(p.ED_UCC_DT IGNORE NULLS) OVER(PARTITION BY p.ULIMORE ORDER BY p.VISIT_DATE_DT ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)

And

LEAD(p.ED_UCC_DT IGNORE NULLS) OVER(PARTITION BY p.ULIMORE ORDER BY p.VISIT_DATE_DT)

Is same as

FIRST_VALUE(p.ED_UCC_DT IGNORE NULLS) OVER(PARTITION BY p.ULIMORE ORDER BY p.VISIT_DATE_DT ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING))

However, I just checked and it appears LEAD IGNORE NULLS is fine:

SQL> CREATE TABLE TEST_LEAD
 2  AS
 3   SELECT ID,
 4       LEAD(CODE ignore nulls) OVER(ORDER BY ID ) CODE
 5    FROM a
 6 /

Table created.

Elapsed: 00:00:00.13
SQL> CREATE TABLE TEST_FIRST_VALUE
 2  AS
 3   SELECT ID,
 4       FIRST_VALUE(CODE ignore nulls) OVER(ORDER BY ID ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) CODE
 5    FROM a
 6 /

Table created.

Elapsed: 00:00:00.11
SQL>

SY.