同事的一个语句执行起来很慢，听说delete几乎执行不动，然后表中记录不过几万条，应该不至于啊，于是感觉上应该是语句不够优化或者是使用了不合适的索引。
首先来看看下面的查询，在没有合适索引的情况下将会有怎样的执行计划：
SQL> select count(*) from servstat where hostip in('0','127.0.0.1');
COUNT(*)
----------
3202637
Elapsed: 00:01:12.74
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13654 Card=1 Bytes=1
2)
1 0 SORT (AGGREGATE)
2 1 PARTITION HASH (ALL)
3 2 TABLE ACCESS (FULL) OF 'SERVSTAT' (Cost=13654 Card=115
950 Bytes=1391400)
Statistics
----------------------------------------------------------
180 recursive calls
0 db block gets
142150 consistent gets
141665 physical reads
0 redo size
494 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
经检查改表有一个唯一索引，可以想象，这里唯一索引显然不合时宜，看看下面的执行计划就知道了：
SQL> select /*+ index(hostip_servstat) */ hostip,count(*) from servstat group by hostip;
HOSTIP COUNT(*)
-------------------- ----------
0 3102880
192.2.2.30 60366
192.2.2.31 1867308
192.2.2.32 1723927
192.2.2.33 1157024
192.2.2.34 1800699
192.2.2.35 1908348
192.2.2.36 1650781
192.2.2.37 1506685
192.2.2.38 1707074
127.0.0.1 99757
11 rows selected.
Elapsed: 00:01:04.99
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=11594969 Byt
es=139139628)
1 0 SORT (GROUP BY NOSORT) (Cost=26 Card=11594969 Bytes=139139
628)
2 1 INDEX (FULL SCAN) OF 'HOSTIP_SERVSTAT' (NON-UNIQUE) (Cos
t=26 Card=11594969 Bytes=139139628)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
61968 consistent gets
20717 physical reads
720 redo size
849 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
SQL>
这列上面建立的unique索引显然不符合要求，因此drop 掉旧的索引，重建为bitmap：
SQL> drop index hostip_servstat;
Index dropped.
Elapsed: 00:00:01.28
SQL>
SQL> Create BITMAP Index hostip_servstat ON servstat(hostip) LOCAL PARALLEL 6 noLOGGING;
Index created.
Elapsed: 00:02:06.95
SQL>
再来看看执行计划：
SQL> select count(*) from servstat where hostip in('0','127.0.0.1');
COUNT(*)
----------
3202637
Elapsed: 00:00:04.21
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=1 Bytes=12)
1 0 SORT (AGGREGATE)
2 1 SORT* (AGGREGATE) :Q296600
0
3 2 PARTITION HASH* (ALL) :Q296600
0
4 3 INLIST ITERATOR* :Q296600
0
5 4 BITMAP CONVERSION* (COUNT) :Q296600
0
6 5 BITMAP INDEX* (SINGLE VALUE) OF 'HOSTIP_SERVSTAT :Q296600
' 0
2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F
ROM (SELECT /*+ NO_EXPAND INDEX_COMB
3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_COMBINED_WITH_PARENT
5 PARALLEL_COMBINED_WITH_PARENT
6 PARALLEL_COMBINED_WITH_PARENT
Statistics
----------------------------------------------------------
20 recursive calls
3 db block gets
139 consistent gets
11 physical reads
856 redo size
494 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
因为，bitmap是依赖于CBO的，因此如果没有analyze的情况下，delete是不会使用到该索引的，请看下面的执行计划：
SQL> delete from servstat where hostip='127.0.0.1' and rownum<2;
1 row deleted.
Elapsed: 00:01:38.62
Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE (Cost=13668 Card=1 Bytes=2
5)
1 0 DELETE OF 'SERVSTAT'
2 1 COUNT (STOPKEY)
3 2 PARTITION HASH (ALL)
4 3 TABLE ACCESS (FULL) OF 'SERVSTAT' (Cost=13668 Card=1
16068 Bytes=2901700)
Statistics
----------------------------------------------------------
0 recursive calls
13 db block gets
396962 consistent gets
15852 physical reads
45528 redo size
1021 bytes sent via SQL*Net to client
1072 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
解决的方法有两个：
1,analyze table
2,使用 NO_EXPAND 和INDEX_COMBINE
例如：
SQL> delete /*+ NO_EXPAND INDEX_COMBINE(T HOSTIP_SERVSTAT) */ from servstat T where hostip='127.0.0.1' and rownum<2;
1 row deleted.
Elapsed: 00:00:08.16
Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE (Cost=19368 Card=1 Bytes=2
5)
1 0 DELETE OF 'SERVSTAT'
2 1 COUNT (STOPKEY)
3 2 PARTITION HASH* (ALL) :Q301000
0
4 3 TABLE ACCESS* (BY LOCAL INDEX ROWID) OF 'SERVSTAT' ( :Q301000
Cost=19368 Card=117976 Bytes=2949400) 0
5 4 BITMAP CONVERSION* (TO ROWIDS) :Q301000
0
6 5 BITMAP INDEX* (SINGLE VALUE) OF 'HOSTIP_SERVSTAT :Q301000
' 0
3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_TO_SERIAL SELECT /*+ NO_EXPAND INDEX_COMBINE(A1 "HOSTI
P_SERVSTAT") */ A1.ROWID,A1."STATID"
5 PARALLEL_COMBINED_WITH_PARENT
6 PARALLEL_COMBINED_WITH_PARENT
Statistics
----------------------------------------------------------
746 recursive calls
21 db block gets
321 consistent gets
41 physical reads
10552 redo size
1013 bytes sent via SQL*Net to client
1127 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
38 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> delete /*+ NO_EXPAND INDEX_COMBINE(T HOSTIP_SERVSTAT) */ from servstat T where hostip='127.0.0.1' and rownum<2;
1 row deleted.
Elapsed: 00:00:04.32
Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE (Cost=19368 Card=1 Bytes=2
5)
1 0 DELETE OF 'SERVSTAT'
2 1 COUNT (STOPKEY)
3 2 PARTITION HASH* (ALL) :Q301100
0
4 3 TABLE ACCESS* (BY LOCAL INDEX ROWID) OF 'SERVSTAT' ( :Q301100
Cost=19368 Card=117976 Bytes=2949400) 0
5 4 BITMAP CONVERSION* (TO ROWIDS) :Q301100
0
6 5 BITMAP INDEX* (SINGLE VALUE) OF 'HOSTIP_SERVSTAT :Q301100
' 0
3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_TO_SERIAL SELECT /*+ NO_EXPAND INDEX_COMBINE(A1 "HOSTI
P_SERVSTAT") */ A1.ROWID,A1."STATID"
5 PARALLEL_COMBINED_WITH_PARENT
6 PARALLEL_COMBINED_WITH_PARENT
Statistics
----------------------------------------------------------
0 recursive calls
17 db block gets
74 consistent gets
3 physical reads
9728 redo size
1017 bytes sent via SQL*Net to client
1127 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>