Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Girish.

Asked: May 31, 2022 - 10:59 am UTC

Last updated: June 01, 2022 - 4:58 pm UTC

Version: 12.2

Viewed 10K+ times! This question is

You Asked

Hi,

1)Among TRUNCATE TABLE and ALTER TABLE SHRINK SPACE command which is more beneficial from space conservation for table.

2)If a table has indexes and will it also be rebuilt/shrink along with truncate/shrink ?

3) Is it better to lock statistics for GTT's ? Should we leave it optimizer to use dynamic sampling from performance perspective

4)We have few staging tables in application which are frequently inserted and deleted. What is recommended to help performance a) lock stats b) reset HWM?

Thanks


and Chris said...

1. These commands solve different problems - you can't compare them directly.

ALTER TABLE ... SHRINK reclaims "wasted" space in the table. For more on this see:

https://asktom.oracle.com/pls/apex/asktom.search?tag=what-is-the-difference-between-shrink-move-and-impdp

TRUNCATE removes all the data from the table! As a side effect, it also deallocates space from the table (by default).

So if you're clearing down a table TRUNCATE is often the way to go. Remember it commits, making it impractical for some use cases. If you need to keep the data, then you'll have to use shrink or move.

2. The indexes are maintained as part of these operations. Shrinking a table won't change the size of any indexes; truncating deallocates index storage as well as the table's:

create table t ( c1 constraint pk primary key, c2 )
enable row movement
as 
  select level c1, level || rpad ( 'stuff', 100, 'f' ) c2 
  from   dual
  connect by level <= 10000;
  
create index i2 on t ( c2 );

delete t
where  c1 <= 9000;
commit;

select segment_name, bytes from user_segments
where  segment_name in ( 'T', 'PK', 'I2' );

SEGMENT_NA      BYTES
---------- ----------
I2            2097152
PK             262144
T             2097152

alter table t shrink space;

select segment_name, bytes from user_segments
where  segment_name in ( 'T', 'PK', 'I2' );

SEGMENT_NA      BYTES
---------- ----------
I2            2097152
PK             262144
T              196608

alter index pk
  rebuild;
  
select segment_name, bytes from user_segments
where  segment_name in ( 'T', 'PK', 'I2' );

SEGMENT_NA      BYTES
---------- ----------
I2            2097152
PK              65536
T              196608

truncate table t;

select segment_name, bytes from user_segments
where  segment_name in ( 'T', 'PK', 'I2' );

SEGMENT_NA      BYTES
---------- ----------
I2              65536
PK              65536
T               65536


3. For what purpose? Note that from 12.1 you can gather session-specific stats for GTTs; this may be the best option:

https://oracle-base.com/articles/12c/session-private-statistics-for-global-temporary-tables-12cr1

4. Are these GTTs or normal tables?

Rating

  (1 rating)

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

Comments

TRUNCATE and SHRINK TABLE

A reader, June 01, 2022 - 12:24 pm UTC

For this question
4)We have few staging tables in application which are frequently inserted and deleted. What is recommended to help performance a) lock stats b) reset HWM?

These tables are normal table (heap tables) and not GTT tables
Chris Saxon
June 01, 2022 - 4:58 pm UTC

If the delete removes all the rows and only one process accesses the tables at a time, you're probably better using TRUNCATE. This solves b for you. This only works if you don't need the DELETE+INSERT process to be transactional.

For a) after the load it's almost certain the high values for some columns will have increased. If you lock the stats, over time this can degrade performance (because the optimizer thinks the maximum value is much lower than it really is).

If the DELETE+INSERT processes change a significant fraction of the rows it can be best to gather stats once this is complete. Locking stats is only really necessary if the default stats gather job is likely to run when the data is very different to how it'll be when you read it.

For example, if the process is INSERT - do stuff - DELETE, so the table is (nearly) empty at the end. If stats gathering runs on the cleared down table, you'll get very unrepresentative stats.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.