Progress
DataServer
for ORACLE Guide


Join by SQL DB

For queries that include joins issued in FOR EACH and OPEN QUERY statements, the DataServer evaluates the queries and, in some cases, instructs the ORACLE DBMS to perform the joins, thereby improving performance. However, when ORACLE performs a join, you receive results in an order consistent with ORACLE, not with Progress. To get results that are consistent with Progress, turn off join by SQL DB with the QUERY-TUNING phrase at the query level or with the -nojoinbysqldb startup parameter when you compile.

For each join, the DataServer evaluates whether it is possible to have the ORACLE RDBMS perform it and estimates whether doing so improves performance. It uses the following criteria to determine whether a join by SQL DB is possible:

The DataServer uses the following criteria to estimate whether performing a join by the ORACLE RDBMS might improve performance:

By default, the DataServer instructs ORACLE to perform a join when possible and when desirable. However, you can control the default behavior by using the QUERY-TUNING [NO-]JOIN-BY-SQLDB phrase or the -nojoinbysqldb startup parameter. The QUERY-TUNING phrase controls the behavior for a single query. The -nojoinbysqldb controls it at the session level. The query-level setting overrides the session-level setting. Table 2–14 describes how these controls interact and affect the behavior.

Table 2–14: Controlling Join by SQLDB 
QUERY-TUNING
Startup Parameter
Behavior
JOIN-BY-SQLDB
-nojoinbysqldb
ORACLE performs the join if possible.
JOIN-BY-SQLDB
None
ORACLE performs the join if possible.
NO-JOIN-BY-SQLDB
-nojoinbysqldb
The client performs the join.
NO-JOIN-BY-SQLDB
None
The client performs the join.
None
-nojoinbysqldb
The client performs the join.
None
None
ORACLE performs the join if possible and if the join is a true join.

Join by SQL DB does not occur by default for the following query:

FOR EACH customer, EACH order: 

You receive a warning if you specify JOIN-BY-SQLDB when it is impossible to have ORACLE perform the join, and the DataServer performs the join instead. You receive a warning at compile time if you specify JOIN-BY-SQLDB when it is not optimal to have ORACLE perform the join.

Improving Join Performance

If a join does not perform as well as you expect, try using the REVERSE-FROM option for the QUERY-TUNING phrase. The REVERSE-FROM option causes the DataServer to generate an SQL FROM clause that lists the tables in reverse order. In some cases, reversing the order in which tables are joined might improve ORACLE DBMS performance.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095