Table Filtering Using Big List

Question

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.

Answer 1

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+)

Answer 2

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.

E.g.

-- 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
SELECT t.*
FROM your_table t
JOIN temp_ids tmp ON t.id = tmp.id;