Does optimizer take into account updates made during same transaction?

Database
Fan

Does optimizer take into account updates made during same transaction?

Hypothetical macro example:

 

REPLACE MACRO DERIVE_TRG(
    DELETE FROM TRG;

    -- For following insert, optimizer correctly estimates,
    -- that approx. 6 million rows will be inserted.
    INSERT INTO TRG SELECT .. FROM SRC_A;

    -- How much rows does optimizer thinks
    -- there are in TRG at this point?
    UPDATE TRG FROM SRC_B SET a=b WHERE ...;
);

Is there a way of forcing optimizer to use estimate from previous statement?

Or optimizer is working strictly within the bounds of a statement?

 

  • optimizer macro
1 REPLY
Junior Contributor

Re: Does optimizer take into account updates made during same transaction?

The optimizer simply applies the current statistics and doesn't adjust it within a MultiStatement Request.

Check Explain and you will see :-)

 

Btw, unless you expect a failure it would be more efficient when submitting individual requests, both DELETE & INSERT will usually be FatsPath without Transient Journal overhead.