Re: What is the purpose of segment level checkpoint before DROP/TRUNCATE of a table?

From: Paresh Yadav <yparesh_at_gmail.com>
Date: Sun, 10 Jul 2011 15:49:14 -0400
Message-ID: <CAPXEL0KzqPAA6GNgfhm-8JOCbxWbOhSRVZKJS=8Amn+gY7kcZQ_at_mail.gmail.com>



Thank you Jonathan. That completes the explanation for Truncate as well.

Here is a thought, I understand we are now getting into "what if oarcle..." part so feel free to ignore this as it is beyond what we can affect Oracle corp. to do.
Is it possible for Oracle to optimize truncate by using a logic similar to delayed block clean out by marking segment header as truncated without clearing the header blocks, extent map blocks etc. and offer cross-DDL (truncate) read consistency? The first process who needs to use truncated object's block will have to do the cleanup. I am sure there is more to the logic than what I have proposed like handling data_object_id etc.

Regards,
Paresh

On Sun, Jul 10, 2011 at 4:14 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk
> wrote:

>
> Truncate leaves an object in place but immediately destroys the segment
> header blocks, extent map blocks, space management blocks and root blocks of
> indexes. It has to do this because the same physical space might be reused
> immediately for more data for the same object.
>
> Drop turns the space into tablespace free space - so there is no question
> of anyone re-using it **for the same object**, so it doesn't overwrite any
> of the existing blocks in the object. That's why cross-DLL read consistency
> is possible on drop - from one user's perspective the object is still there,
> from another user's perspective there is no object at all just free space.
>
> I still can't think of any reason why this should have any connection with
> recovery in general and PITR
>
>
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.**wordpress.com <http://jonathanlewis.wordpress.com>
>
>
> ----- Original Message ----- From: "Paresh Yadav" <yparesh_at_gmail.com>
> To: <jonathan_at_jlcomp.demon.co.uk>
> Cc: "free" <oracle-l_at_freelists.org>
> Sent: Sunday, July 10, 2011 6:12 AM
> Subject: Re: What is the purpose of segment level checkpoint before
> DROP/TRUNCATE of a table?
>
>
>
> Everyone, Thanks for trying to answer my question.
>>
>> Jonathan, Your answer explains "why for drop". Why do you consider it as a
>> side effect on truncate for piggy backing on same code? i.e. What is wrong
>> with Truncate honoring Cross-DDL read-consistency?
>>
>> Can you connect this with anything regarding recovery and PITR?
>>
>> Thanks,
>> Paresh
>>
>>
> --
> http://www.freelists.org/**webpage/oracle-l<http://www.freelists.org/webpage/oracle-l>
>
>
>

-- 
Thanks
Paresh
416-688-1003

--
http://www.freelists.org/webpage/oracle-l
Received on Sun Jul 10 2011 - 14:49:14 CDT

Original text of this message