One db too many

Hi all,

I am stuck here with a slight performance problem. I have a two-database environment, the big O and the free My alternative...moving all of the data to Oracle is unfortunately not an option for various reasons.

I have a perl script that interacts with both databases. It retrieves a list of id numbers from the mysql database and puts these in an array. The number of ids can be anything from 5 to 50,000. I now want to do this:

SELECT DISTINCT field FROM table WHERE id IN (<ids in array>)

table is a ~1.2 million rows Oracle table, primary key on id of course.

So far i have tried the following:

1. WHERE id IN (x, y, z, ...) Ids joined to a long comma-delimited string. This works well until number of ids > 1000 and i get "ORA-01795: maximum number of expressions in a list is 1000"
2. WHERE id = x OR id = y OR id = z OR id = ... This works, but is way too slow for anything more than 5,000 ids
3. loop over array, insert into dummy table in oracle, and join So far this is the best alternative, but gets pretty slow (20-30 seconds) with many ids because of having to loop over the array and do one insert per id. The insert is done with a placeholder for id, like so: INSERT INTO dummy (id) VALUES (?), and AutoCommit is off.

Is there a 4th, 5th, 6th, ... alternative or is there a way to improve 3)? Any and all hints/suggestions would be appreciated.

Thanks in advance,

Joakim Mared Received on Mon Jan 14 2002 - 10:10:40 CET