sql query hangs/times out when passed large data

UPDATED: Guys I am passing list of faculty id’s to stored procedure, when i pass more than 10 faculty id’s it’s taking long time to process the data and sometime times out. I spent good amount of time on debugging this query and find out the last line in proc is causing query to timeout/slow response time. The last line is subquering from CTE. How to I rewrite/optimize last line of the proc to make my query run faster. Any help will be highly appreciated. Can someone help on this please.

create or replace PROCEDURE sp_Test(
facultycode IN  varchar2
//few more variables
---
---
p_result OUT sys_refcursor
)
AS
open recordset for 
//this parses list of id's i.e('101''102''104'108') and i am calling this in last line of my proc
with faculty_list as (
SELECT REGEXP_SUBSTR(facultycode,'[^,]+',1,LEVEL) AS FAC_CODE
         FROM DUAL              
    CONNECT BY LEVEL <=LENGTH (REGEXP_REPLACE(facultycode,'[^,]+'))+1
    )

select s.s_name, m.score, s.status
 from student s 
join marks m on s.s_id = m.s_id 
WHERE S.GRADE>5
AND facultycode is null or s.fac_code in (select FAC_CODE from faculty_list); //this line 
is making query very slow, I need to optimize this line for faster response
END sp_Test;

Answer

You can try using the /*+ MATERIALIZE */ hint on the faculty_list with clause sub query. In general, I’d say the performance would mostly depend on if student.faculty_code is indexed, you can add to this index the grade

create index student_fac_grade_idx on student (fac_code, grade);

If you have this index and the materialize hing, then you have the optimal chance of this performing well. The following works for null input list as well:

with in_list as
(
select '101,102,104,108,201,202,204,208,301,402,504,608' in_list from dual
--select '' in_list from dual
)
, faculty_list as 
(
SELECT /*+ MATERIALIZE */ REGEXP_SUBSTR(in_list,'[^,]+',1,LEVEL) AS FAC_CODE
         FROM DUAL , in_list    
    CONNECT BY LEVEL <=LENGTH (REGEXP_REPLACE(in_list,'[^,]+'))+1
)
--select * from faculty_list ;
, student as
(
select 1 s_id, 'Joe' s_name, 'ACTIVE' status , 6 grade, 101 fac_code from dual
)
, marks as
(
select 1 s_id, 100 score from dual
)
/* main query */
select s.s_name, m.score, s.status
from student s 
join marks m on s.s_id = m.s_id 
WHERE S.GRADE>5
and (s.fac_code in (select FAC_CODE from faculty_list)
or (select fac_code from faculty_list ) is null)
;