同事的一个语句执行起来很慢,听说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)