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?
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.