OBIEE - How to control the use of the WITH CLAUSE and of STITCH Join ?

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Server (OBIS|nqsserver|nqs)

1 - About

The physical sql issued when you have designed a vertical fragmentation depend of several database features parameters:

  • PERF_PREFER_MINIMAL_WITH_USAGE: if checked, the WITH clause is not used
  • PERF_PREFER_INTERNAL_STITCH_JOIN: if checked, the STITCH joins have your favor.
  • WITH_CLAUSE_SUPPORTED: the database support the WITH clause
  • FULL_OUTER_JOIN_SUPPORTED: the database support the FULL OUTER JOIN clause.

FULL_OUTER_JOIN_SUPPORTED because the vertical fragmentation made use of a full outer join against the conformed dimension.

PERF_PREFER_MINIMAL_WITH_USAGE and PERF_PREFER_INTERNAL_STITCH_JOIN was introduce to resolve the issues with Full Outer Join and WITH Clause on Oracle Database 10g

Advertising

3 - One Query without the WITH Clause

  • Uncheck PERF_PREFER_INTERNAL_STITCH_JOIN
  • Check PERF_PREFER_MINIMAL_WITH_USAGE
  • Check FULL_OUTER_JOIN_SUPPORTED
  • Always true when WITH_CLAUSE_SUPPORTED is unchecked
SELECT DISTINCT CASE  WHEN D2.c2 IS NOT NULL THEN D2.c2 WHEN D1.c2 IS NOT NULL THEN D1.c2 END  AS c1,
     D1.c1 AS c2,
     D2.c1 AS c3
FROM
     (SELECT SUM(T245.QUANTITY_SOLD) AS c1,
               T161.CHANNEL_DESC AS c2
          FROM
               SH.CHANNELS T161,
               SH.SALES T245
          WHERE  ( T161.CHANNEL_ID = T245.CHANNEL_ID )
          GROUP BY T161.CHANNEL_DESC
     ) D1 FULL OUTER JOIN (SELECT SUM(T168.UNIT_COST) AS c1,
               T161.CHANNEL_DESC AS c2
          FROM
               SH.CHANNELS T161,
               SH.COSTS T168
          WHERE  ( T161.CHANNEL_ID = T168.CHANNEL_ID )
          GROUP BY T161.CHANNEL_DESC
     ) D2 ON D1.c2 = D2.c2
ORDER BY c1

4 - Two Queries

  • Check PERF_PREFER_INTERNAL_STITCH_JOIN
  • Uncheck PERF_PREFER_MINIMAL_WITH_USAGE
  • Always true when FULL_OUTER_JOIN_SUPPORTED is unchecked
-------------------- Sending query to database named orcl SH (id: <<5084>>):
SELECT T161.CHANNEL_DESC AS c1,
     SUM(T245.QUANTITY_SOLD) AS c2
FROM
     SH.CHANNELS T161,
     SH.SALES T245
WHERE  ( T161.CHANNEL_ID = T245.CHANNEL_ID )
GROUP BY T161.CHANNEL_DESC
ORDER BY c1
 
 
-------------------- Sending query to database named orcl SH (id: <<5121>>):
 
SELECT T161.CHANNEL_DESC AS c1,
     SUM(T168.UNIT_COST) AS c2
FROM
     SH.CHANNELS T161,
     SH.COSTS T168
WHERE  ( T161.CHANNEL_ID = T168.CHANNEL_ID )
GROUP BY T161.CHANNEL_DESC
ORDER BY c1

5 - One query with the WITH clause

  • Uncheck PERF_PREFER_INTERNAL_STITCH_JOIN
  • Uncheck PERF_PREFER_MINIMAL_WITH_USAGE
  • check WITH_CLAUSE_SUPPORTED
  • Check FULL_OUTER_JOIN_SUPPORTED
WITH
SAWITH0 AS (SELECT SUM(T245.QUANTITY_SOLD) AS c1,
     T161.CHANNEL_DESC AS c2
FROM
     SH.CHANNELS T161,
     SH.SALES T245
WHERE  ( T161.CHANNEL_ID = T245.CHANNEL_ID )
GROUP BY T161.CHANNEL_DESC),
SAWITH1 AS (SELECT SUM(T168.UNIT_COST) AS c1,
     T161.CHANNEL_DESC AS c2
FROM
     SH.CHANNELS T161,
     SH.COSTS T168
WHERE  ( T161.CHANNEL_ID = T168.CHANNEL_ID )
GROUP BY T161.CHANNEL_DESC)
SELECT DISTINCT CASE  WHEN SAWITH1.c2 IS NOT NULL THEN SAWITH1.c2 WHEN SAWITH0.c2 IS NOT NULL THEN SAWITH0.c2 END  AS c1,
     SAWITH0.c1 AS c2,
     SAWITH1.c1 AS c3
FROM
     SAWITH0 FULL OUTER JOIN SAWITH1 ON SAWITH0.c2 = SAWITH1.c2
ORDER BY c1
Advertising

6 - Documentation / Reference