OPT_NOT_EXISTS_FROM

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.

See also:

• Load options

• OPT_NOT_EXISTS_WHERE

Usage

OPT_NOT_EXISTS_FROM: sql_code

Notes

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.

Examples

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:

OPT_NOT_EXISTS_FROM: <target_schema>.<target_entity_name>_CUSTOM_CURRENT_VIEW

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
  );