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. – a_horse_with_no_name Jun 24 at 8:46

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+)
| improve this answer | |
  • 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 at 12:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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