queries across multiple databases

general discussion
1

Dear all
We are looking at the implications of performing queries on multiple tables across multiple databases.

It would appear to be possible to hide (from Business Objects) the fact that there are multiple databases as described in the scenarios below. We are interested in finding out if there are other facilities within Business Objects that could(should) be used to achieve the same result. Any comments on the scenarios and their analysis would be appreciated as well.

Base assumption - There are two tables (A and B) joined by a common field ©. Both Table A and B have many rows, the join of the two tables has only few rows.

Scenario 1 - Both Table A and B are in the one Oracle database Business objects will generate a query ‘SELECT A.E, B.F FROM A,B WHERE A.C =B.C’
This query would be passed to the Oracle database server, processed and the rows returned to the Business Objects application. RESULT: An efficient query (assuming appropriate indexes and clusters)

Scenario 2 - Table A is in Oracle database 1 and Table B is in Oracle database 2. Database 1 & 2 are on the one Oracle server. Oracle database links have been established between the databases Business objects will generate a query ‘SELECT A.E, B.F FROM A,B WHERE A.C =B.C’
This query would be passed to the Oracle database server. The first database will then generate a query to the second database '‘SELECT B.F FROM B’ and will store the results in a temporary table. It will then use this temporary table to resolve the original query.
RESULT: A less efficient query (particularly if '‘SELECT B.F FROM B’ returns many rows)

Scenario 3 - Table A is in Oracle database 1 and Table B is in Oracle database 2. Database 1 & 2 are on different Oracle servers. Oracle database links have been established between the databases As for scenario 2 except that temporary table has to traverse the network RESULT: A far less efficient query (particularly if '‘SELECT B.F FROM B’ returns many rows)

Scenario 4 - Table A is in Oracle database 1 and Table B is in SQL Server 7 database 2.
Database links have been established between the databases Theoretically, database links can be used to get an Oracle database to communicate with a non-Oracle database. This would result in a similar situation to that described in Scenario 3. Is this linking possible. RESULT: A similar result to Scenario 3 (assuming that it works).

Listserv Archives (BOB member since 2002-06-25)

2

I guess my first question is, in scenario 2, why do you have two different databases? It would be far more efficient two have to schemas on the same database. That way, you don’t have the separate query populating the temp table - all Oracle has to do is have access to the other schema via a synonym or equivalent mechanism, or you can use an alias in your universe.

As for scenarios 3 and 4, I understand the reasons for wanting to set up the architecture that way, but I think you’re a masochist if you really want to do it. Don’t go there, is my advice. :slight_smile:

Paul

Listserv Archives (BOB member since 2002-06-25)

3

Fiona,

I tend to agree with Paul.

But to answer your question regarding additional functionality in BusinessObjects … this is a tool which provides the thin layer or template facility of a Universe. The Universe contains pointers to your database/s and predefined SQL for the data manipulation. At query time the SQL is generated and sent to the database. From this point onwards, how your backyard (i.e. database) is structured will effect performance … BusinessObjects can’t change that. The SQL can be finetuned to accommodate best SQL / indexing etc, but cannot affect a better performance than an SQL to separate databases / servers will afford.

Unless of course you utilise Stored Procedures … but that detracts from the flexibility of Object/Universe open architecture for the end user.

Regards,
Ang.

Listserv Archives (BOB member since 2002-06-25)