Re: Accessing Corba through ODBC with ORACLE

In article <3A2CF6A7.51352650_at_groton.pfizer.com>,   Amalio Escobar <amalio_escobar_at_groton.pfizer.com> wrote:
> We have a performance problem with Oracle accessing a Corba datasource
> that we are exposing through ODBC. When we do a Join between a native
> Oracle table and the Corba data source the optimizer chooses a merge
> join, thus pulling all the records from the Corba datasource. What we
> would like is an inner loop join, where the keys from the native
 Oracle
> table are passed to the Corba data source through ODBC.
>
> We have described to the Oracle Open gateway the number of rows in our
> Corba data source (three million), the key (which is the attribute we
> are joining on) and the description of all the other attributes. Is
> there a way we can persuade Oracle to pass to our ODBC driver (that we
> are writing with ATI's ODBC SDK) a query such as
>
> select * from CORBA_SOURCE
> where CORBA_KEY in (k1, k2, ..., kn)
>
> where k1, k2,..., kn are the join values from the native Oracle table?
>
> Amalio
> amalio_escobar_at_groton.pfizer.com
>
>

Your description seems reasonable. If I were designing a database, I would do a merge-join as it is the only reliable solution. You are running up against the reason that people want all there data at least in the same type of database if not in the same database. I have never tried what you are describing when looking into two different Oracle instances. Simply cases ( table 1 in one instance and table 2 in the other instance ) and small datasets, Oracle can use indexes in both instances. However performance is always slower than when the tables are in the same instance.

Have you tried to
Either

        select * from CORBA_SOURCE
        where CORBA_KEY in ( select k from ORACLE_SOURCE )

OR assuming that you are programming this, make the process two step

        select k from ORACLE_SOURCE

        build select * from CORBA_SOURCE statement

        execute CORBA_SOURCE statement

I assume that the Oracle Gateway has knowledge of indexes on the CORBA sources.

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rosetta.org
              Ususual disclaimers


Sent via Deja.com http://www.deja.com/
Before you buy.