一个用 Oracle 函数索引进行优化的例子

表中有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的改进吧。