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;