Querying Oracle with a pick list

Question

I have an oracle database that I have read-only access (with no permission to create temporary tables). I have a pick list (in Excel) of 28000 IDs corresponding to 28000 rows in a table which has millions of records. How do I write a query to return the 28000 rows?

I tried creating a table in access and performing a join through ODBC but Access freezes/takes an incredible long time. Would I have to create a query with 28,000 items in an IN statement?

Is there anything in PL/SQL that would make it easier?

Thank you for your time and help.

-JC

Answer 1

The max number of variables for an IN (.., .. ,,) type query is 1000 in Oracle 10g.

Answer 2

What makes your 28,000 rows special?

Is there another field in the records you can use to restrict you query in a WHERE clause (or at least narrow down the millions of rows a bit)? Perhaps the ID's you're interested in fall within a certain range?

Answer 3

Try creating an index on the table you created in Access.

Answer 4

That's a painful condition to be in. One workaround is to create a view that contains all of the ids, then join to it.

The example below is Oracle.

WITH
ids AS
(
    SELECT 314 ID FROM DUAL UNION ALL
    SELECT 159 ID FROM DUAL UNION ALL
    SELECT 265 ID FROM DUAL
)
SELECT VALUE1, VALUE2
FROM SOME_TABLE, ids
WHERE SOME_TABLE.ID = ids.ID

This basically embeds all 28000 ids, in the with clause, allowing you to do the join, without actually creating a table.

Ugly, but it should work.

Answer 5

The best way to do it is described here: http://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause/400295#400295