Re: ITL related question

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_at_sleepnumber.com> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l