为SQL创建合理的函数索引

在测试环境中创建表,

create table ash_tmp as select * from dba_hist_active_sess_history;

给以下SQL设计合理的索引, 并解释创建改索引的原因。

select * from ash_tmp where to_char(sample_time,'YYYYMMDD-HH24') between
'20181128-09' and '20181128-10' and program like '%LGWR%' and event = 'log file parallel write';

该语句当前执行计划如下:

       查看涉及到的列的统计信息：

select c.column_name,t.num_rows,c.num_distinct from dba_tables t,
dba_tab_columns cwhere t.owner=c.owner and t.table_name=c.table_name andt.table_name='ash_tmp' and column_name in (upper('sample_time'),upper('program'),upper('event'));
 
select count(*),count(distinct sample_time),count(distinct program),count(distinct event),count(distinct sample_time||’’ ||program||’’||event) from ash_tmp;

可以看到sample_time列选择度较高，所以为其创建单列索引；

索引创建后查看执行计划发现to_char的用法导致发生了函数转换，所以创建函数索引，再次查询，发现执行计划走了索引：

--create index idx_ash_tmp__time on ash_tmp(sample_time);
create index idx_ash_tmp_h_time on ash_tmp(to_char(sample_time,'yyyymmdd-hh24'));