Home » RDBMS Server » Performance Tuning » Does CTAS uses unused space??
Does CTAS uses unused space?? [message #430762] Thu, 12 November 2009 04:54 Go to next message
sameertuladhar
Messages: 12
Registered: November 2008
Location: Nepal
Junior Member
I have been using CTAS option for very big size tables. But I have few queries on the space utilization while using CTAS option.

Does it reuses the fragmented space in the tablespace if there is any. I mean to say if my table space is fragmented due to frequent addition and deletion of objects like tables and not shrinked then obviously there would be some unused space left. If I then use CTAS option to create new table then would it use that unused space or it would simply use new space leaving the defragemented or unused space untouched.

Any suggestions would really be helpful.

Regards,
Sameer.
Re: Does CTAS uses unused space?? [message #430776 is a reply to message #430762] Thu, 12 November 2009 05:37 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no fragmentation with LMT.

Regards
Michel
Re: Does CTAS uses unused space?? [message #430787 is a reply to message #430762] Thu, 12 November 2009 05:58 Go to previous messageGo to next message
sameertuladhar
Messages: 12
Registered: November 2008
Location: Nepal
Junior Member
Thank you for the quick reply,

I think I may not have explained my problem well. I will try this time in detail.

Actually what happened is that I have a schema which was occupying space of approx 150 gb. On checking its actual used space from dba_segments I found that it only used 70 gb. Basically I use ctas to populate tables into this schema from other schemas.

Could it be the case that ctas operation is resulting in the unexpected growth in the size of this schema? Or could it be simply because ctas is not using the free space which may have been there because of deletion of older tables in the schema where I have not done shrink operations...

Here's the sample block of code I have used..


CREATE TABLE abc.xyz
STORAGE (INITIAL 10M NEXT 1M MAXEXTENTS UNLIMITED)
PARALLEL (DEGREE 4)AS
SELECT a.*
FROM
def.xyz a,
ghi.pqr b
WHERE a.er= b.er

NB:The real code is a bit more dynamic than this.

Regards,
Sameer
Re: Does CTAS uses unused space?? [message #430825 is a reply to message #430787] Thu, 12 November 2009 07:32 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Could it be the case that ctas operation is resulting in the unexpected growth in the size of this schema?

The "size of the schema" is the sum of the size of its current objects. If you add one then the size increases. If you delete one then the size decrease. There is no relation with fragmentation.

Deleted objects leaves free space, free space is used for new allocations (segment or extend).

Regards
Michel
Re: Does CTAS uses unused space?? [message #431048 is a reply to message #430825] Fri, 13 November 2009 17:18 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Or, put more simply, space freed up by dropping objects can be used by CTAS, and in fact by any other statement that creates a segment.

Just make sure your tablespaces are Locally Managed, not Dictionary Managed.

Ross Leishman
Previous Topic: User statistics
Next Topic: .trc files
Goto Forum:
  


Current Time: Mon Sep 26 14:31:42 CDT 2016

Total time taken to generate the page: 0.10610 seconds