OPT_NOT_EXISTS_FROM is a load option that enables customization of the FROM command in the WHERE NOT EXISTS clause in automatically generated loads to improve query performance in some cases.
OPT_NOT_EXISTS_FROM can be used in loads that have a single entity mapping (i.e. one source entity) and no OVERRIDE load steps. OPT_NOT_EXISTS_FROM will not have an effect if the load is overridden with an OVERRIDE load step.
Optimize satellite load by using a custom current view
By default, Agile Data Engine creates a current view for each satellite with a default logic. Sometimes it might be necessary to optimize performance by manually rewriting this logic in a separate custom current view. Current views are used in loading satellite tables to check incoming staging data against existing current data in the satellite.
In this example a custom current view is used in a satellite load with the OPT_NOT_EXISTS_FROM load option. Note the generic use of <target_schema> and <target_entity_name> variables:
DML generated by Agile Data Engine:
INSERT INTO dv.S_TAXI_ZONE ( ... ) SELECT DISTINCT ... FROM ( SELECT ... FROM stage.STG_TAXI_ZONE_LOOKUP src_entity ) src WHERE NOT EXISTS ( SELECT 1 FROM dv.S_TAXI_ZONE_CUSTOM_CURRENT_VIEW trg WHERE trg.dv_datahash = src.dv_datahash );