04-23-2014 02:09 PM
How can I free up the disk space after deleting the data from table? I used Proc SQL - Delete From Statement to delete data from table. However, table size is not reduce.
04-23-2014 03:16 PM
In oracle, When we DELETE rows from the table, all the data get copied into the Rollback Tablespace first then delete operation is performed. ROLLBACK after deleting a table, can bring back deleted data and a COMMIT can remove the rows permanently from the table and this causes to reduce size of table. I would recommend use pure SAS for this.
set have (obs=0);
Insert rows which need to be retained in have from the main table and drop the main table. In that you will have a table occupying less space.
04-23-2014 05:32 PM
SAS is great for many things, but data management on a row level isn't one of them.
Deletion of single records will only mark them in the data set as logically deleted. Therefore you won't see any change in file size.
However, if you have compressed tables, you can also chose to have a dataset attribute (option) reuse=yes, this will save disk space over time. The risk with this method is that the internal structure of the data set will be cluttered, and could decrease performance.
So, if you have the possibility to rewrite the whole table, it's the cleanest solution.
If you have some data set attributes you wish to keep, such as indexes and constraints, they will be lost using techniques mentioned above. An easy way to prevent this is bt using proc apend (deleted is a temporary table):
proc append data=lib.have(where=(your subset criteria)) base=lib.deleted;
proc datasets lib=lib nolist;