Controlling Outer Join Optimization

How to:

Reference:

With the SET SQLJOIN OUTER command, you can control when the adapter optimizes outer joins without affecting the optimization of other operations. This parameter provides backward compatibility with prior releases of the adapter and enables you to fine-tune your applications.

When join optimization is in effect, the adapter generates one SQL SELECT statement that includes every table involved in the join. The RDBMS can then process the join. When join optimization is disabled, the adapter generates a separate SQL SELECT statement for each table, and FOCUS processes the join.

You can use the SQLJOIN OUTER setting to disable outer join optimization while leaving other optimization enhancements in effect.


Top of page

x
Syntax: How to Control Outer Join Optimization
SQL sqlengine SET SQLJOIN OUTER {ON|OFF}

where:

sqlengine

Indicates the target RDBMS. Valid values are DB2, SQLDBC, or SQLORA. Omit if you issued the SET SQLENGINE command.

ON

Enables outer join optimization. ON is the default value for Teradata and Oracle.

OFF

Disables outer join optimization. OFF is the default value for DB2.

Note:


Top of page

x
Reference: Effects of Combinations of Settings on Outer Join Optimization

The following table describes how different combinations of OPTIMIZATION and SQLJOIN OUTER settings affect adapter behavior. It assumes that SET ALL = ON:

Settings

Results

OPTIMIZATION

SQLJOIN OUTER

Outer Join Optimized?

Other Optimization Features

ON

ON

Yes

Enabled

ON

OFF

No

Enabled

OFF

N/A

No

Disabled

SQL

ON

Yes, in all possible cases

Enabled

SQL

OFF

No

Enabled

FOCUS

ON

Yes if results are equivalent to FOCUS-managed request

Enabled

FOCUS

OFF

No

Enabled



Example: Enabling Left Outer Join Optimization

The following request specifies a left outer join between the EMPINFO and FUNDTRAN tables. The SQLJOIN OUTER setting specifies that the left outer join should be optimized, and the SQLAGGR and STMTRACE trace components are activated:

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
 
SET ALL = ON
SQL DB2 SET OPTIMIZATION ON
SQL DB2 SET SQLJOIN OUTER ON
JOIN EMP_ID IN EMPINFO TO ALL WHO IN FUNDTRAN AS J1
TABLE FILE EMPINFO
SUM AVE.CURRENT_SALARY ED_HRS BY WHO BY LAST_NAME
IF DEPARTMENT EQ 'MIS'
END

The following trace is generated. One SELECT statement is generated. The LEFT OUTER JOIN phrase in the FROM predicate specifies the left outer join:

AGGREGATION DONE ...
 SELECT T2."EID",T1."LN", AVG(T1."CSAL"), SUM(T1."OJT") FROM (
USER1."EMPINFO" T1 LEFT OUTER JOIN "USER1"."FUNDTRAN" T2 ON
T2."EID" = T1."EID") WHERE (T1."DPT" = 'MIS') GROUP BY
T2."EID",T1."LN" ORDER BY T2."EID",T1."LN" FOR FETCH ONLY;

Information Builders