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

Bi Server Architecture With Client

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

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

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

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

Documentation / Reference





Discover More
Obiee Database Features
OBIEE - Database Features Parameters

When you create a database in the OBIEE physical layer some databases features parameters are automatically checked depending on the type of your database. database section This features are mostly...
Obiee Fact Cross Join
OBIEE - Densification with the fact-based fragmentation capabilities

You may want to view the data in dense form, with rows for all combination of dimension values displayed even when no fact data exist for them. The preservation of the dimensions is is also well known...
Bi Server Architecture With Client
OBIEE - Fact-based vertical partitioning/fragmentation

This technique allow you to : mix two facts table with two different grains (the level based partitioning) perform a densification (to preserve dimension value) : partition vertically your fact...
Obiee Logical Join Bmm
OBIEE - Full outer Join

To perform a , you have two ways : in the repository by using the fact vertical partitioning capabilities or with the Obiee logical sql To know what sort of join is a full outer join, follow this...
Obiee Query Log
OBIEE - Physical SQL

The physical SQL is the SQL that send the BI Server to the data sources in order to retrieve data. This SQL is most of the time generated by the query compiler during the query processing of the logical...



Share this page:
Follow us:
Task Runner