表中有500万条记录,原来没有索引:
set timing on set autotrace traceonly SQL> select count(*), to_char(time,'hh24') from userloginlog 2 where trunc(time) = trunc(sysdate) - 1 3 group by to_char(time,'hh24') 4 order by to_char(time,'hh24');
24 rows selected. Elapsed: 00:00:06.70 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6179 Card=37215 Bytes=297720) 1 0 SORT (GROUP BY) (Cost=6179 Card=37215 Bytes=297720) 2 1 TABLE ACCESS (FULL) OF 'USERLOGINLOG' (TABLE) (Cost=6039 Card=37257 Bytes=298056) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 25154 consistent gets 24470 physical reads 0 redo size 763 bytes sent via SQL*Net to client 514 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 24 rows processed
查询使用全表扫描,执行需6.7秒。
下面建立函数索引:
create index idx_time on userloginlog (to_char(time,'hh24')) tablespace indexes; create index idx_time2 on userloginlog (trunc(time)) tablespace indexes;
执行同样的查询:
SQL> select count(*), to_char(time,'hh24') from userloginlog 2 where trunc(time) = trunc(sysdate) - 1 3 group by to_char(time,'hh24') 4 order by to_char(time,'hh24');
24 rows selected. Elapsed: 00:00:00.34 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=323 Card=37215 Bytes=297720) 1 0 SORT (GROUP BY) (Cost=323 Card=37215 Bytes=297720) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'USERLOGINLOG' (TABLE) (Cost=183 Card=37257 Bytes=298056) 3 2 INDEX (RANGE SCAN) OF 'IDX_TIME2' (INDEX) (Cost=64 Card=16143) Statistics ---------------------------------------------------------- 197 recursive calls 0 db block gets 341 consistent gets 1 physical reads 0 redo size 763 bytes sent via SQL*Net to client 514 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 24 rows processed
查询使用索引扫描,执行需0.34秒,快了20倍。
还有一点,建立索引后并没有执行 analyze table userloginlog compute statistics; 进行分析,索引就生效了,这是10g的改进吧。