对一张1000多条记录的小表做count居然花了8秒多。
记录一下解决过程：
SQL> set timing on;
SQL> select count(1) from t_temp;
COUNT(1)
----------
1227
Executed in 8.171 seconds
SQL> desc t_temp;
Name Type Nullable Default Comments
---------- ------------ -------- ------- --------
PROPOSALNO VARCHAR2(50) Y
POLICYNO VARCHAR2(50) Y
QUERYNO VARCHAR2(35) Y
DEMANDNO VARCHAR2(50) Y
CONTRACTNO VARCHAR2(35) Y
SQL> col SEGMENT_NAME for a20;
SQL> select segment_name, sum(bytes) / 1024 / 1024 mb
2 from user_segments u
3 where u.segment_name in ('t_temp', 'IDX_t_temp')
4 group by segment_name;
SEGMENT_NAME MB
-------------------- ----------
t_temp 296.77
IDX_t_temp 494.58
一共1227条数据，居然占用率296MB的空间，IDX_t_temp 将近500MB。
不用做set autotrace 和10046 了，估计是碎片惹的祸。
回收一下表空间，看看效果。
SQL> alter index IDX_t_temp shrink space;
SQL> alter table t_temp enable row movement;
SQL> alter table t_temp shrink space;
SQL> analyze table t_temp estimate statistics;
SQL> select count(1) from t_temp;
COUNT(1)
----------
1227
Executed in 0.094 seconds