Briefly, I am trying to filter a table using conditions plus a list of items in Oracle. In detail, I have a reporting table which has millons of records. I would like to filter it using external lists which might include more than 1000 lines of fields from the record table. (Obviously, I especially said 1000, because in clause in Oracle has the limit of 1000 items). So my question is; How can I effectively filter a huge table using another big list.

  • 1
    One workaround for the limit of a 1000 values is to use a multi-column IN condition. where (1, the_column) in ( (1,100), (1, 200), (1, 300) ) - but that won't be very efficient either.
    – user1822
    Jun 24, 2020 at 8:46

2 Answers 2


How can I effectively filter a huge table using another big list?

Just JOIN the big table to list.

Just make sure your list is a table format:

  • External Table
  • Global Temporary Table
  • APEX Collection (for APEX apps)
  • an SQL collection (variable)
    • PL/SQL collection (18c+)
  • Thanks for your suggestion. I will use this in my Hibernate + Spring application and the list will be submitted from UI. I think none of these will fully get my requirement done. I think I will need to tweak it on UI via paging data tables :). And cut down some filter functions. All I can do is this now :)
    – Olgun Kaya
    Jun 24, 2020 at 12:55

Create a temporary table then store the values in smaller lists.

then join your main table with the temporary table on the appropriate column to filter the data.


-- Create a temporary table
CREATE GLOBAL temporary table temp_ids (id int);

-- Insert your IDs into the temporary table

-- Join the main table with the temporary table to filter the data
FROM your_table t
JOIN temp_ids tmp ON t.id = tmp.id;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.