Skip to main content

In USoft V9.1, if we use an Usage outer join in combination with batch parameterset, it looks like the optimizer removes the outer join instruction and executes an inner join, e.g. 

update p_set par 

set column_1 = (

select     round(nvl(sdurd.duration, adv.value))
        from    ops_stage_duration_default sdurd
        ,        app_default_values adv
        where    par.from_poi_id = sdurd.from_poi_id (+)
        and        par.to_poi_id = sdurd.to_poi_id (+)
        and        ptpc.from_poi_id = par.from_poi_id 
        and        adv.name = 'STAGE_DEFAULT_DURATION'
        )

)

Start Benchmark and you’ll see the outer join removed

 

Is this intended behavior?

Hi Jan,

I tried to reproduce this, but with USoft version 9.1.1U I see an outer join in the profile.
My job has one SQL task and two statements, the first sets the duration, see below, the second would add missing stage durations (but stage 1-6 exists).

My job invocation:

invoke batchrunner.GET_DURATION
with
select
	1 "POI_ID_FROM"
,	6 "POI_ID_TO"

The first SQL statement:

update	PAR_DN par
set
	DURATION =
(
	select
		NVL( sdn.DURATION
		,	acn.CONFIG_VALUE
		)
	from
		STAGE_DURATION sdn,
		APP_CONFIGURATION acn
	where
		par.POI_ID_FROM = sdn.POI_ID_FROM (+)
	and
		par.POI_ID_TO = sdn.POI_ID_TO (+)
	and
		acn.APPLICATION_NAME = rulesEngine.getProperty( 'application' )
	and
		acn.CONFIG_NAME = 'Default duration'
)

This is the RDBMS statement for the (reason) new values:

SELECT NVL(t2.DURATION, t3.CONFIG_VALUE)
FROM T_TSET_2428112C622601B08_1 t1 LEFT OUTER JOIN ROBERT91.STAGE_DURATION t2 ON t1.POI_ID_TO = t2.POI_ID_TO AND t1.POI_ID_FROM = t2.POI_ID_FROM, ROBERT91.APP_CONFIGURATION t3
WHERE (t3.APPLICATION_NAME = :1 AND t3.CONFIG_NAME = 'Default duration' AND (t1.POI_ID_FROM IS NULL AND TO_NUMBER(:2) IS NULL OR t1.POI_ID_FROM = TO_NUMBER(:2)) AND (t1.POI_ID_TO IS NULL AND TO_NUMBER(:3) IS NULL OR t1.POI_ID_TO = TO_NUMBER(:3)) AND (t1.DURATION IS NULL AND TO_NUMBER(:4) IS NULL OR t1.DURATION = TO_NUMBER(:4)))
(TRAVEL,1,6,NULL)

Notice the left outer join. Surprisingly, it uses a temporary table. Good to know, I used Oracle RDBMS for testing.


Could you compare your benchmark RDBMS statement to mine? I'm curious to hear what causes the difference.


Regards,

Robert

Reply


Terms & ConditionsCookie settings