Proposal for Improving the Performance of “Copy From Project”

Our customer has a large number of activities associated with each project.
As a result, executing “Copy From Project” takes a very long time.
We consider modifying the SQL statement in “SUB_PROJECT_API,” which is called during this process.
Please provide your comments and advice on the proposed changes below.

[Before]
FUNCTION Get_Total_Key_Path_ (
project_id_ IN VARCHAR2,
sub_project_id_ IN VARCHAR2 ) RETURN VARCHAR2
IS
total_key_path_ VARCHAR2(200);
CURSOR Get_Path IS
SELECT REVERSE(key_path) total_key_path FROM
(SELECT SYS_CONNECT_BY_PATH(REVERSE(sub_project_id), '^')||'^' key_path
FROM sub_project_tab
WHERE project_id = project_id_
CONNECT BY PRIOR parent_sub_project_id = sub_project_id
AND PRIOR project_id = project_id
START WITH sub_project_id = sub_project_id_)
ORDER BY key_path DESC
FETCH FIRST 1 ROWS ONLY;
BEGIN
OPEN Get_Path;
FETCH Get_Path INTO total_key_path_;
CLOSE Get_Path;
RETURN NVL(total_key_path_, text_separator_);
END Get_Total_Key_Path_;

[After]
FUNCTION Get_Total_Key_Path_ (
project_id_ IN VARCHAR2,
sub_project_id_ IN VARCHAR2 ) RETURN VARCHAR2
IS
total_key_path_ VARCHAR2(200);
CURSOR Get_Path IS
SELECT REVERSE(key_path) total_key_path FROM
(SELECT SYS_CONNECT_BY_PATH(REVERSE(sub_project_id), '^')||'^' key_path
FROM (select * from sub_project_tab where project_id = project_id_)　/* modify point  */
WHERE project_id = project_id_
CONNECT BY PRIOR parent_sub_project_id = sub_project_id
AND PRIOR project_id = project_id
START WITH sub_project_id = sub_project_id_)
ORDER BY key_path DESC
FETCH FIRST 1 ROWS ONLY;
BEGIN
OPEN Get_Path;
FETCH Get_Path INTO total_key_path_;
CLOSE Get_Path;
RETURN NVL(total_key_path_, text_separator_);
END Get_Total_Key_Path_;

[Before]
FORALL i IN activities_tab_.FIRST .. activities_tab_.LAST
UPDATE activity_tab a
SET total_key_path = (SELECT REVERSE(key_path) FROM
(SELECT SYS_CONNECT_BY_PATH(REVERSE(sub_project_id), '^')||'^' key_path
FROM sub_project_tab
WHERE project_id = new_project_id_
CONNECT BY PRIOR parent_sub_project_id = sub_project_id
AND PRIOR project_id = project_id
START WITH sub_project_id = a.sub_project_id)
ORDER BY key_path DESC
FETCH FIRST 1 ROWS ONLY)
WHERE project_id = new_project_id_
AND sub_project_id = activities_tab_(i).sub_project_id
AND node_type = 'SUB_PROJECT';

[After]
FORALL i IN activities_tab_.FIRST .. activities_tab_.LAST
UPDATE activity_tab a
SET total_key_path = (SELECT REVERSE(key_path) FROM
(SELECT SYS_CONNECT_BY_PATH(REVERSE(sub_project_id), '^')||'^' key_path
FROM (select * from sub_project_tab where project_id = project_id_)　/* modify point */
WHERE project_id = new_project_id_
CONNECT BY PRIOR parent_sub_project_id = sub_project_id
AND PRIOR project_id = project_id
START WITH sub_project_id = a.sub_project_id)
ORDER BY key_path DESC
FETCH FIRST 1 ROWS ONLY)
WHERE project_id = new_project_id_
AND sub_project_id = activities_tab_(i).sub_project_id
AND node_type = 'SUB_PROJECT';