Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Rob.

Asked: March 21, 2002 - 8:45 pm UTC

Last updated: March 04, 2005 - 11:33 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Tom:

I have a data warehouse table that is loaded with parallel dml,
i.e. insert select stmt with append hint. Before the load, I
truncate the table. Is there any advantage to using the reuse
storage clause in thee truncate. This table is only loaded once, no other dml takes
place. I am assuming from info that I have gleaned from searching
your archive that there should be no free list for this table,
therefore the truncate table reuse storage will cause all blocks
to be below the high water mark. I am wondering if blocks go on
the free list only from delete and update stmts? Would doing a
standard insert and then truncate table reuse storage cause blocks
to go on the free list? Do doing a truncate table drop storage
get rid of the free list?

Thanks as always,

Rob

and Tom said...

the blocks will only go on free lists dues to updates and deletes and NORMAL non-append inserts (an insert of a new row that finds the freelists empty will get some blocks from beyond the HWM and put them on the freelist if the new row does not exceed the pctfree) -- truncate resets the HWM and obliterates the freelists (regardless of how you use truncate).

You should be using locally managed tablespaces and I would not save the extents (i'd truncate back to nothing) and then insert. The newly allocated extents (only as many as you need) will be allocated near the "front" of the existing datafiles effectively reusing the space as needed.

Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Further question

Rob Balter, March 22, 2002 - 11:24 am UTC

If I have a table with 100 allocated extents and I do
a truncate table reuse storage, will the direct path insert
reuse the 100 extents for data or create an additonal
100 extents.

Does a partitioned table have freelists per partition or
per table.

Tom Kyte
March 22, 2002 - 12:21 pm UTC

direct path INSERTS insert above the HWM, into existing allocated but not used space. So, yes, it'll reuse them if they are "empty" and above the HWM.

each partition will have its own list since a block can only belong to one partition (so they must be separated out)

Irfan, March 25, 2002 - 2:18 am UTC

Hi Tom,
a) I would like to know how the one block is created in free list as normally after deletion it goes into free list.

SQL> create table t ( x int, y char(2000) default '*' )
  2  storage ( initial 40k next 40k minextents 5 )
  3  tablespace system;

Table created.

SQL> insert into t (x) values ( 1 );

1 row created.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> compute sum of blocks on report
SQL> break on report
SQL> select extent_id, bytes, blocks
  2  from user_extents
  3  where segment_name = 'T'
  4  and segment_type = 'TABLE';

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         2      61440         15
         3     102400         25
         4     143360         35
         0      40960         10
         1      40960         10
                      ----------
sum                           95

SQL>  clear breaks
breaks cleared
SQL> select blocks, empty_blocks, avg_space, num_freelist_blocks
  2  from user_tables
  3  where table_name = 'T';

    BLOCKS EMPTY_BLOCKS  AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
         1           93       1971                   1

b) In one of your followup you have mentioned the first block of each table is used by the system. So it means
totally 96 blocks are allocated to this table?

c) Your solution
(an insert of a new row that finds the freelists empty will 
get some blocks from beyond the HWM and put them on the freelist if the new row does not exceed the pctfree)
Can you please explain - put them on the freelist if the new row does not exceed the pctfree -

Thanks. 

Tom Kyte
March 25, 2002 - 8:02 am UTC

a) blocks are on the freelist UNTIL they exceed their pctfree. Assuming an 8k blocksize and 10 pctfree, you would have to insert about 3 rows before that block would be taken OFF of the freelist in the first place (all blocks originate on the freelist for normal inserts like that).

It stays on the freelist till pctfree is exceeded, then comes back on after it hits pctused. If you have my book -- I go over this in some detail in the chapter on tables.

b) search this site for show_space. It will show you exactly what is allocated, used, on the freelist, etc. You have 95 blocks. One of them is in use by the system. One of them is in use by you.

Irfan, March 25, 2002 - 1:25 pm UTC

Hi Tom,

I checked the followin link; you have explained clearly;

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:231414051079,%7Bshow_space%7D <code>

but still I could not able to make out, considering the followup (example), if one block is used by system, then the EMPTY_BLOCKS should be 92. Becuase one is used by me, one in freelist and the one by the system. Can you please clear me.

Thanks...

Tom Kyte
March 25, 2002 - 2:51 pm UTC

One is used by you AND it is the same one that is on the freelist. The block with your single row is the same block as is on the freelist.

You are double counting your block.

Empty Freelist

Vikas Sharma, July 08, 2002 - 6:35 am UTC

I read in your book and in this thread also, that the block which are above the high water marks are included in to freelist when freelist are empty. Can you please tell when freelist get empty.

Tom Kyte
July 08, 2002 - 7:44 am UTC

when you use them and they exceed their pctfree and get taken off of the freelist. So, when a table is "full" and the very next insert would cause it to allocate another extent.

Freelist

Vikas Sharma, July 09, 2002 - 1:28 pm UTC

Thanks Tom it clears my doubt.
Thanks


More clarifications

Sanjaya Balasuriya, April 04, 2004 - 1:53 am UTC

Hi Tom,

Say we set free lists to 20 for a table.
Then 20 free block are kept reserved for the table ?

And what is the impact of free list groups on a table ?

Since to set the value for free list we should know the concurrent access (updates or inserts), how can I know the number of concurrent access ? Is there any views to get this information ?


Thanks in advance.

Tom Kyte
April 04, 2004 - 8:40 am UTC

no, 20 free blocks are not kept, it means you have 20 pointers to 20 lists.

do you have my book Effective Oracle by Design? see:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:12209165088566 <code>
for a short extract on freelist groups.


You (as the designer of the application) needed to have that sort of information during design time (in order to properly build the application). If you did not, you just have to "guess" -- no, there are no v$ views that'll tell you the maximum number of concurrent transactions -- but you as the designer should have a pretty good gut feel.

Truncate Table with Reuse Storage

Senthil, June 03, 2004 - 1:34 am UTC

sir,

What is difference between Truncate Table without Reuse Storage and Truncate Table with Reuse Storage clause.

thanks

pointers to freelists

Sam, March 04, 2005 - 9:59 am UTC

<quote>it means you have 20 pointers to 20 lists.<quote>

(1) say I have 20 freelists and if 20 processes simultaneously inserting rows, I was wondering how does 20 processes access 20 freelist that could all be in the same block. Does oracle use the concept of ITLs as in the data block so that multiple processes can access the same block?

(2)Conceptually, can I relate 20 freelists as 20 rows in a block so that multiple processes can access its own freelist just like multiple processes can update different rows at the same time in a block as long as space is there to for all ITLs? Thanks.

You are awesome!

Tom Kyte
March 04, 2005 - 10:58 am UTC

1) a block will be on a single freelist. not sure what you meant.

2) 20 freelists is just that -- 20 separate and mutually exclusive list of blocks.

pointers to freelist

Sam, March 04, 2005 - 11:24 am UTC

I understand. However, all of the information related to say, 10 freelists are stored in one header block. right? Considering this, now multiple processes access the same block to get a freelist. How does oracle do? I was wondering is it similar to multiple processes accessing a diffent row in the same block for updates for example using ITLs? Hope my question makes some sense. I don't want to waste your time. Thanks.

Tom Kyte
March 04, 2005 - 11:33 am UTC

see Note 157250.1 on metalink, very good discussion, has pictures to boot