oracle查詢變慢

Question

Oracle中一张table做insert之后，500W条数据，做系统的测试。测试完之后，把500W数据都删除掉了。但是为何查询的速度还是比原来慢非常多（已经是空表了）。

Answer 1

你用的delete删除的？
应该是碎片的原因 
用truncate这个

Answer 2

给你做个测试。。-------没建表的时候，没有你的段信息，段代表你的对象在数据库的分配的存储信息
SQL> select segment_name,bytes from dba_segments where owner = upper('tiny') and segment_name = upper('xieyijun');SEGMENT_NAME                                                                          BYTES
-------------------------------------------------------------------------------- ----------SQL> create table tiny.xieyijun(id number);Table created
--------创建一张表，分配空间
SQL> select segment_name,bytes from dba_segments where owner = upper('tiny') and segment_name = upper('xieyijun');SEGMENT_NAME                                                                          BYTES
-------------------------------------------------------------------------------- ----------
XIEYIJUN                                                                              65536-------插入20000条记录，分配的空间由于65536变为了262144
SQL> edit
SQL> /PL/SQL procedure successfully completedSQL> commit;Commit completeSQL> select segment_name,bytes from dba_segments where owner = upper('tiny') and segment_name = upper('xieyijun');SEGMENT_NAME                                                                          BYTES
-------------------------------------------------------------------------------- ----------
XIEYIJUN                                                                             262144
---------删除所有记录后，分配的空间不变，这个就是HWM的值
SQL> delete from tiny.xieyijun;20000 rows deletedSQL> commit;Commit completeSQL> select segment_name,bytes from dba_segments where owner = upper('tiny') and segment_name = upper('xieyijun');SEGMENT_NAME                                                                          BYTES
-------------------------------------------------------------------------------- ----------
XIEYIJUN                                                                             262144
---------truncate/drop掉后，存储空间被释放。hWM减少
SQL> truncate table tiny.xieyijun;Table truncatedSQL> select segment_name,bytes from dba_segments where owner = upper('tiny') and segment_name = upper('xieyijun');SEGMENT_NAME                                                                          BYTES
-------------------------------------------------------------------------------- ----------
XIEYIJUN                                                                              65536SQL> drop table tiny.xieyijun;Table droppedSQL> select segment_name,bytes from dba_segments where owner = upper('tiny') and segment_name = upper('xieyijun');SEGMENT_NAME                                                                          BYTES
-------------------------------------------------------------------------------- ----------SQL>