Re: One db too many

Thank you for your reply Stefan,

I do, however, think you misunderstood me a bit. The array of ids is from another, non-oracle source. I have made attempts with inserting the array of ids into a 'dummy' table in Oracle and then join with that dummy table, but i still have problems with the inserts being too slow.

I know that my design is not a very common one, but it is actually thought through, and switching to 100% Oracle is unfortunately not an option.

Joakim

"Stefan" <sroesch3_at_attbi.com> skrev i meddelandet news:cWO08.31747$JF.309777_at_rwcrnsc52.ops.asp.att.net...
> Hi Joakim,
>
> seems to me you have a major problem with your relational design and that
> one entity is missing. Create that table and use it in the join
>
> Stefan
>
>
> Joakim Mared wrote:
>
> > 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 Tue Jan 15 2002 - 08:21:20 CET