The optimization process and recommendations:

1. Avoid Calling Functions With Indexed Columns (query line: 49): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `sysdate` is indexed, the index won’t be used as it’s wrapped with the function `TRUNC`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
2. Avoid Calling Functions With Indexed Columns (query line: 56): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `edge_vp` is indexed, the index won’t be used as it’s wrapped with the function `substr`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
3. Avoid Calling Functions With Indexed Columns (query line: 60): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `edge_rm` is indexed, the index won’t be used as it’s wrapped with the function `substr`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.

The optimized query:

SELECT
        efh.snapshot_date,
        max(efhp.snapshot_date) AS previous_snapshot_date,
        substr(efh.edge_vp,
        1,
        instr(efh.edge_vp,
        '@oracle.com') - 1) AS edge_vp,
        substr(efh.edge_rm,
        1,
        instr(efh.edge_rm,
        '@oracle.com') - 1) AS edge_rm,
        sum(CASE 
            WHEN efh.oppty_status = 'Open' THEN NVL(efh.ARR_FORECAST,
            0) 
            ELSE 0 END) AS forecast,
sum(CASE 
    WHEN efh.oppty_status = 'Open' THEN NVL(efh.ARR_BEST,
    0) 
    ELSE 0 END) AS best,
sum(CASE 
    WHEN efh.oppty_status = 'Won' THEN NVL(efh.ARR,
    0) 
    ELSE 0 END) AS closed,
sum(CASE 
    WHEN efh.oppty_status = 'Open' THEN nvl(efh.ARR_PIPELINE,
    0) 
    ELSE 0 END) AS pipeline,
sum(CASE 
    WHEN efh.oppty_status = 'Open' THEN NVL(efh.ARR_BEST,
    0) 
    ELSE 0 END) + sum(CASE 
    WHEN efh.oppty_status = 'Open' THEN nvl(efh.ARR_PIPELINE,
    0) 
    ELSE 0 END) AS pipe_best,
sum(CASE 
    WHEN efh.oppty_status = 'Won' THEN efh.ARR 
    ELSE 0 END) + sum(CASE 
    WHEN efh.oppty_status = 'Open' THEN NVL(efh.ARR_FORECAST,
    0) 
    ELSE 0 END) AS closed_forecast 
FROM
edge_forecast_hist efh 
LEFT JOIN
edge_forecast_hist efhp 
    ON efhp.edge_vp = efh.edge_vp 
    AND efhp.edge_rm = efh.edge_rm 
    AND efhp.snapshot_date < efh.snapshot_date 
WHERE
efh.snapshot_date >= TRUNC(sysdate) - INTERVAL '70' DAY 
AND efh.edge_asm != 'REDACTED' 
AND efh.oppty_status IN (
    'Open', 'Won'
) 
GROUP BY
efh.snapshot_date,
substr(efh.edge_vp,
1,
instr(efh.edge_vp,
'@oracle.com') - 1),
substr(efh.edge_rm,
1,
instr(efh.edge_rm,
'@oracle.com') - 1) 
ORDER BY
1,
2,
3,
4