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
Articles Related
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