1

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

BIBD
  • 15,107
  • 25
  • 85
  • 137
JC.
  • 307
  • 1
  • 5
  • 11

5 Answers5

3

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?

Graham
  • 14,885
  • 4
  • 36
  • 42
  • The IDs are actually a combination of a phone number and a time. That's pretty much what makes the row unique. – JC. Jun 25 '09 at 21:33
  • 2
    I believe Graham is asking what differentiates your 28,000 rows from the millions of rows in the table that you're not interested in. Can you devise a query that finds those 28,000 rows that doesn't involve 28 separate IN lists of 1000 literals a piece? – Justin Cave Jun 25 '09 at 21:50
  • +1, hits the nail on the head. If you can't come up with an answer to this, you're in for one of the painful solutions that others have given. – DCookie Jun 29 '09 at 16:38
  • They do fall within a certain date range but so do hundreds of thousands of other records. I'm still a bit confused at how this will help me other than perhaps making the query a bit faster. Wouldn't I still have to write multiple IN lists? – JC. Jun 30 '09 at 18:02
0

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

Gandalf
  • 9,648
  • 8
  • 53
  • 88
0

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

Mark Roddy
  • 27,122
  • 19
  • 67
  • 71
0

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.

EvilTeach
  • 28,120
  • 21
  • 85
  • 141
0

The best way to do it is described here: How to put more than 1000 values into an Oracle IN clause

Community
  • 1
  • 1
Theo
  • 819
  • 4
  • 6