Question

oracle数据库，有CLOB字段的表在删除过数据后直接select * from tablename 查询为何很慢？

我有一个系统的日志表，存在两个大字段CLOB，存储的是XML报文，之前数据量达到了300万，为了核查日志更加快速，我写了个存储过程将其按照日志月份分别存放在月表中，然后使用delete 删除掉了其中的数据。
奇怪的事情就此发生了，第二天系统照常运行，日志表中只有新插入的5000条数据，可是我直接使用
select * from tablename查询，竟然用了60秒才出来，
然而使用select * from tablename where to_char(op_time,'yyyymmdd')='20131002' 来查询，居然只用了15秒
请教诸位大神，这是神马情况，
以下为PL/SQL中得到的查询计划
select * from tablename

select * from tablename where to_char(op_time,'yyyymmdd')='20131002'

焦急等待中！！！！！！！！！！！！！！
简单表结构如下，字段太多，有21个，没法全粘贴上来
create table WPT_LOG
(
OP_ACCEPT NUMBER(14) not null,
...............................................................
CHANNEL_ID VARCHAR2(50),
INF_OUT_PARA CLOB, -----大字段
INF_IN_PARA CLOB)





Answer 1

因为delete过后是不移动HW的，而Oracle进行全表扫描时，是需要读取HW以前的全部块的，即使是空块也要扫描，举例来说，原来你的表是这样的，每个字母代表一个块，D是数据，H是HW标记，F是空块，此时全表扫描是读所有的D块
DDDDDDDDDDHFFFFFFFFFFF

当你delete删除时，H是不移动的
DDFFFFFFFFFHFFFFFFFFFFF
此时H前的D和F都要扫描，你需要收缩表移动HW，变成这样
DDHFFFFFFFFFFFFFFFFFFFF
这样它只扫描两个D

所以你要收缩一下你的表，移动HW
先允许表能够row movement
alter table wpt_log enable row movement;
再收缩，
alter table wpt_log shrink space;
收缩完后，最好给这表做一次统计分析
exec dbms_stats.gather_table_stats(user,'WPT_LOG',cascade=>true)

如果需要删除表中所有数据，建议用truncate，它可以将HW移动到表头
HFFFFFFFFFFFFFFFFFFFFFF
再插入数据时，HW会按照你填充块自动顺序移动

还有，如果你做开发，下面sql是不合适的，尽量不要在表列上加函数（执行用15秒，因为它是并发的）
select * from tablename where to_char(op_time,'yyyymmdd')='20131002';
如果op_time保存时所有行都没有带时间,上面sql可以改为：
select * from tablename where op_time=to_date('20131002','yyyymmdd')
如果在op_time上有个索引，那么上面sql可能会走索引

Answer 2

select * from tablename查询，竟然用了60秒才出来，
然而使用select * from tablename where to_char(op_time,'yyyymmdd')='20131002' 来查询，居然只用了15秒

有where 和没有where的查询速度能比吗

追问

但是在5000条数据的前提下，效率相差也太大了吧，
我把原表create as aaa from tablename拷贝之后，select * from aaa只用了3秒就查出来了
你看执行计划，耗费那么高，肯定有问题啊