Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

> ## 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 the Progress 4GL. To get results that are consistent with the Progress 4GL, 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. The DataServer uses the following criteria to determine whether a join by SQL DB is possible:

> - All tables in the join are in the same logical OpenEdge database, that is, they are contained in the same DataServer schema. The tables can be in distributed ORACLE databases as long as they are represented in a single DataServer schema.

> - Every table, except the innermost one, has a unique record identifier (`ROWID`) or `RECID` support.

> - There is no `USING` phrase for any of the inner tables. For example, join by SQL DB will not occur for this query:

>     FOR EACH customer, EACH order OF customer USING order-num:

> - There is no `BY` phrase that contains expressions or array fields.

> - There is no request for an `EXCLUSIVE-LOCK` on any of the tables in the join.

> - The join does not exceed 10 levels.

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

> - The join uses an `OF` clause or `WHERE` clause for each of the inner table loops. For example, the following query requires a field-to-field correspondence between two tables:

>     FOR EACH customer, EACH order OF customer:

> - The `WHERE` clause includes the equals operator (=) and the `AND` option, as in the following example:

>     FOR EACH customer, EACH order
>       WHERE customer.cust_num = order.cust_num AND customer.cust_num > 100:

> 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 4–2 describes how these controls interact and affect the behavior.

> Table 4–2: 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 © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095