The ITL is at the block level, it attempts to update it just before it
updates the block for the update. Otherwise, since it doesn’t know which
rows will be updated until the statement is executed fully, it would need
to update the ITL of every block.
Some DBMSs do use lock escalation under similar conditions as they store
locks outside of the data making it not scalable to assign many locks.
Oracle does not have this problem.
Thanks,
Andrew
On Fri, 6 Aug 2021 at 14:58, Hameed, Amir <amir.hameed@xxxxxxxxxxxxxxx>
wrote:
Hi,
When Oracle runs a DML statement that contains a subquery, does Oracle
grab an ITL entry immediately at the start of the statement or does it wait
until row-set from the subquery/subqueries are returned? The reason I am
asking is that if it is the former then depending on the efficiency of the
subquery, it could occupy the ITL for a while?
Thanks,
Amir
