PM11141: SQL GENERATED FOR FULL OUTER JOINS IN ORACLE 10G IS INEFFICIENT

APAR status

• Closed as program error.

Error description

• The customer has a model that contains a full outer join.?
  ???????       In 8.2, a report that used the full outer join in
  the model ran in 27 seconds??????? ???????       In 8.4 the same
  model, database, and report take over an hour to run, and while
  running several????  DPR-ERR-2002, unable to execute due to no
  connections to? process...  errors are generated.???? ???????
     The model has a governor set that limites the query run time
  to one hour.  The problems seem to all stem from the fact that
  the query is taking longer than one hour.???? ???????       In
  examining the SQL generated by 8.4, it is clear that the SQL
  creates a full cross product between ALL rows of the two tables
  in the full outer join, then filters on the results of that
  table???????? ???????       For large tables such as the
  customer has, this is extremely    inefficient.  The same
  results can be generated in approximately the same time it took
  8.2 to generate the results if the tables are filtered first,
  then joined, rather than joined then        filtered.???????
  ???????       ???????? Customer env is:?????        Suse 10
  Linux??????        Webshere (using the plugin rather than a
  Cognos gateway)        Oracle 10g??????? ???????       I was
  reproduced in house on????      Windows XP??????
  Tomcat???????        Oracle 10g??????? ???????       The same
  issue may exist with SQL Server and other RDBMS, but   wasn't
  tested.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Users                                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See error description.                                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to IBM Cognos Business Intelligence 10.1 Fix Pack 1  *
  * or Upgrade to IBM Cognos 8 Business Intelligence 8.4.1 Fix   *
  * Pack 4                                                       *
  ****************************************************************
  

Problem conclusion

• Code Fix
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  COG8 BI COG CON

• Fixed component ID

  5724W12CC

Applicable component levels

• RA10 PSN

     UP