浅谈如何删除表中大量数据及删除数据后查询速度优化

浅谈如何删除表中大量数据及删除数据后查询速度优化背景：近日，本人接到一个任务，是关于数据库中某个表中存在大量的历史数据，表中每天大约插入约100W行数据，客户反映查询速度很慢，要求解决：任务：该数据库是关于治安卡口的数据库，其中一张表（clxx_b）是记录过车信息的表。该表按月进行分区，每个分区对应一个表空间，该表现有4亿多行数据，分区从11年5月到12年6月。现在该表插入数据缓慢，查询该表数据已经很慢了，客户无法忍受了，要求我们尽快解决；针对任务；采用删除表分区，然后删除相应表空间的方法,按照客户要求，保留最近三个月的数据，删除11年5月至12年3月的分区及其表空间、数据文件，所用SQL语句如下：删除表分区altertableclxx_bdroppartitionclxx_201105;…………………………………………………………..altertableclxx_bdroppartitionclxx_201203;删除相应表空间及数据文件droptablespaceclxx_1105includingcontentsanddatafiles;……………………………………………………………………….droptablespaceclxx_1203includingcontentsanddatafiles;执行该命令后，发现数据文件还在，并没有被删除，可按以下操作，右键“我的电脑”-“管理”-“服务”，停止orcl服务然后再磁盘下删除相应数据文件，路径F:\oracle\product\10.2.0\oradata\orcl下，删完数据文件后再重启ORCL服务。删除这些数据以后，执行selectcount(*)fromclxx_b,发现很久没反应，比删除分区前更慢，这是因为该表实际占用的表空间变小了(selectsum(bytes/1024/1024)MBfromdba_segmentswheresegment_name=’CLXX_B’)，占用的空闲表空间变大了，解决的办法就是释放表空间，执行altertableclxx_bmove;执行完后，再执行selectsum(bytes/1024/1024)MBfromdba_segmentswheresegment_name=’CLXX_B’，可以看到，占用的空间变小了，如果没有变小，说明释放空间没有成功，可以尝试执行以下命令其中一条：altertableclxx_bdeallocateUNUSEDKEEP0;analyzetableclxx_bcomputestatistics;altertableclxx_bshrinkspace;这样处理后表空间就释放了。但是这样处理后，表的行号rowid就会发生变化，而基于rowid的索引则会变成无效：selectindex_name,statusfromdba_indexeswhereowner=’uesr_name’。因此该操作后必须重建索引，否则会提示“ORA-01502:索引‘XXX‘或这类索引的分区处于不可用状态”，可以用alterindexindex_namerebuild;重建索引。索引重建以后，发现查询速度快了很多很多，数据插入速度也快了很多，至此，任务圆满完成。