An example of Oracle function index Optimization

The table contains 5 million records, which are not indexed:

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 cballs
0 db block gets
25154 consistent gets
24470 physical reads
0 redo size
763 bytes sent via SQL * Net to client
514 bytes encoded ed via SQL * Net from client
3 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
24 rows processed

It takes 6.7 seconds to perform a full table scan.

Create a function index as follows:

Create index idx_time on userloginlog (to_char (time, 'hh24') tablespace indexes;

Create index idx_time2 on userloginlog (trunc (time) tablespace indexes;

Execute the same query:

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 cballs
0 db block gets
341 consistent gets
1 physical reads
0 redo size
763 bytes sent via SQL * Net to client
514 bytes encoded ed via SQL * Net From Client
3 SQL * Net roundtrips to/from client
6 sorts (memory)
0 sorts (Disk)
24 rows processed

Index scanning is used for queries. It takes 0.34 seconds to execute the queries, which is 20 times faster.

Another point is that after the index is created, the analyze table userloginlog compute statistics is not executed; after the index is analyzed, the index takes effect. This is also a 10g improvement, which is good.