Optimize SQL query in form of a Procedure I have a Oracle SQL query with multiple "IN" statements. It takes around a minute to execute in an average.

My task is to optimize this query in form of PL/SQL procedure so that the user gets the output resultset as a ref cursor.

Please suggest. Thanks!

SQL Query:

select sp.column1, sp.column2,sp.column3,gen.column1,nvl(gen.column2,0)+nvl(gen.column3,0) GENSIZE,gen.column4,gen.column5 
from table1 sp,
(select * from table2 where table2.column1 in (
select table1.column4 from table1 where table1.column2 in (
select table3.column1 from table3 where table3.column2='124578'))) gen
where gen.id=sp.id;
As far as returning a ref cursor the following should work;

CREATE OR REPLACE PROCEDURE get_table1_data(p_column2 IN table3.column2%TYPE, 
                                            p_tables_recs  OUT SYS_REFCURSOR ) AS
BEGIN

   OPEN p_table_recs FOR 
      SELECT sp.column1
           , sp.column2
           , sp.column3
           , gen.column1
           , NVL(gen.column2, 0) + NVL(gen.column3, 0) gensize
           , gen.column4
           , gen.column5
      FROM   table1 sp
          , (SELECT *
             FROM   table2
             WHERE  table2.column1 IN (SELECT table1.column4
                                       FROM   table1
                                       WHERE  table1.column2 IN (SELECT table3.column1
                                                                 FROM   table3
                                                                 WHERE  table3.column2 = p_column_2))) gen
       WHERE  gen.id = sp.id;
END get_table1_data;