Oracle - large table with large IN
Data Comes from External Source

The IN clause is limited to 1000 values.  Don't use it for such searches.

Workaround:


Use a GTT (Global Temporary Table)
insert "values of interest" into that GTT
Modify the SELECT statement to JOIN against the GTT.


Example

Create the GTT

create global temporary table students_gtt (
  student_id int primary key
);


Insert values into the GTT

insert into students_gtt
select level
from dual
connect by level &lt;= 50000;


and JOIN it in the SELECT statement

select
    *
from
    students       s
    left join grades         g on s.id = g.student_id
    join students_gtt   g on s.student_id = g.student_id


Notes


First, I suspect a good portion of your time is spent Hard Parsing your query.


I'm taking a 22,974,957 row table and left outer joining it to a 93,501,293 row table in addition to joining the 50,000 row GTT.  The values return in seconds.

Eventually, you are going to a run into a "node" limit for the parser.
The GTT method does not require you to "hack your SQL statement".


Data from another SQL Statement?

Just include that SQL within the SELECT statement.