On shrinking table sizes
来源:互联网 发布:网络胜利组 百度网盘 编辑:程序博客网 时间:2024/04/30 01:15
Usually, this is a good thing because the 'deleted' space will be used for future insert statements. Also update statements that increase the data size can use such 'deleted' space. Technically, this is because Oracle does not move the high water mark downwards. However, when there is a mass delete operation that won't be followed by insert or update statements, it might be advisable to really free the deleted space. In order to demonstrate this, I create a table and insert 10000 records:
create table table_size_test (
a char(100),
b number
) storage (initial 65K next 65K pctincrease 0)
tablespace ts_01;
begin
for i in 1 .. 10000 loop
insert into table_size_test values
(dbms_random.string('X', 100),i);
end loop;
end;
/
commit;
I also create an index on the table:
create index ix_table_size_test on table_size_test(a)
storage (initial 65K next 65K pctincrease 0)
tablespace ts_02;
Let's see how much space the table and index use:
select substr(segment_name,1,20) segment, bytes / 1024 "Size [KB]" from user_segments
where segment_name in ('TABLE_SIZE_TEST', 'IX_TABLE_SIZE_TEST');
SEGMENT Size [KB]
-------------------- ----------
TABLE_SIZE_TEST 1280
IX_TABLE_SIZE_TEST 1280
Half of the records in the table are deleted:
delete from table_size_test where mod(b,2)=0;
commit;
No space is freed:
select substr(segment_name,1,20) segment, bytes / 1024 "Size [KB]" from user_segments
where segment_name in ('TABLE_SIZE_TEST', 'IX_TABLE_SIZE_TEST');
SEGMENT Size [KB]
-------------------- ----------
TABLE_SIZE_TEST 1280
IX_TABLE_SIZE_TEST 1280
Using alter table .. move defragments (if you want that expression) and stores the table more efficiently:
alter table table_size_test move;
select substr(segment_name,1,20) segment, bytes / 1024 "Size [KB]" from user_segments
where segment_name in ('TABLE_SIZE_TEST', 'IX_TABLE_SIZE_TEST');
SEGMENT Size [KB]
-------------------- ----------
TABLE_SIZE_TEST 640
IX_TABLE_SIZE_TEST 1280
However, there are two problems. First, the size of the index is not reduced. But even worse: the index is invalidated!:
select status from user_indexes
where index_name = 'IX_TABLE_SIZE_TEST';
STATUS
--------
UNUSABLE
The index needs to be rebuilt:
alter index ix_table_size_test rebuild;
This not only validates the index again, but also shrinks its size:
select status, bytes/1024 from
user_indexes join user_segments on
index_name = segment_name
where index_name = 'IX_TABLE_SIZE_TEST';
STATUS BYTES/1024
-------- ----------
VALID 704
Thus, using alter table move, the allocated size on the harddisk for table and index had been roughly decreased by 50%.
- On shrinking table sizes
- weblogic Shrinking
- Shrinking Segments
- [已解决]Error:Removing unused resources requires unused code shrinking to be turned on.
- Stones on the Table
- CREATE TABLE ON [PRIMARY]
- Football on Table
- Stones on the Table
- Sensor Sizes
- Shrinking Polygons hust 14048
- Shrinking Database Segments Online
- linux partition table on PC
- Cursor Position on Table Controls
- 物化视图 on prebuilt table
- A. Stones on the Table
- BNU 34986 Football on Table
- Failed - Error on Table table_name!
- coderforces Stones on the Table
- 2007年下半年国家软考指定教材
- 欧洲最大IT展会CeBIT开幕 绿色IT是主题
- NetBeans IDE 6.1 Beta Now Available!
- 2007年 上半年信息系统监理师 下午试卷
- 微软2008年产品列表
- On shrinking table sizes
- 2007年 上半年信息系统监理师 上午试卷
- doc
- 在ASP.NET中使用LINQ(第一部分)
- 2006年 下半年信息系统监理师
- VS2008针对VS2005的性能改进
- Google网络广告女副总裁投奔Facebook担任COO
- 2006年 上半年信息系统监理师
- C#事件学习