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 ABL. To get results that are consistent with ABL, turn off join by SQL DB with the QUERY-TUNING phrase at the query level or with the -nojoinbysqldb startup parameter when you compile.

In DataServers, JOIN-BY-SQLDB is the default behavior for outer join operations.

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.

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:

By default, dynamic, inner, and outer joins are performed by the oracle database server, provided the criteria set by the DataServer is fulfilled. For more information on joins, see OpenEdge Data Managemment: SQL Development.

An Unknown value (?) in OpenEdge is mapped to a NULL value in MS SQL Server; When you perform a JOIN-BY-SQLDB, MS SQL does not join the records in the parent table to the records in the child table on NULL join key values. For an outer join performed with JOIN-BY-SQLDB, if the results set contains a parent record with a NULL join key value and a non-matching child record, all field values in the child record are set to NULL. In contrast, when you perform a join by client, records that are joined on the Unknown value (?) (to which NULLs correspond), the results set includes any matching parent and child records in the join results of both inner and outer joins.

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. The following table describes how these controls interact and affect the behavior.

Table 24. 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:

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.