如何简化和减少特定日期格式的sql查询的执行时间？

Question

我正在使用带有以下查询和代码的jdbcTemplate

    String sql = select count(*) as count ,trunc(fin.fin_date) as dateAndTime from  (select * from rec where rec_status='RECEIVED' and fin_date>sysdate-8) fin group by trunc(fin.fin_date)

    try {
        return jdbcTemplate.query(sql, new RowMapper<Chrt>() {
            @Override
            public Chrt mapRow(ResultSet rs, int rowNum) throws SQLException {
                Chrt receive = new Chrt();
                java.util.Date date =(rs.getDate("dateAndTime"));
                receive.setDate(date.toString());
                receive.setCount(rs.getInt("count"));
                return receive;
            }
        });
    } catch (DataAccessException e) {
        return null;
    }
 }

它以类似于2016-05-19的格式返回我的日期，但我需要像19-5-16这样的日期。有没有可能在不增加执行时间的情况下获得这种格式？

我该怎么做呢？

Answer 1

您可以使用TO_CHAR函数来代替trunc函数。它将为您提供指定格式的日期：

String sql = select count(*) as count ,TO_CHAR(fin.fin_date, 'DD-MON-YY') as dateAndTime from  (select * from rec where rec_status='RECEIVED' and fin_date>sysdate-8) fin group by TO_CHAR(fin.fin_date, 'DD-MON-YY');

这不会显著影响性能。

为了提高性能，我建议您查看查询的查询计划。要查看此计划，只需执行：

EXPLAIN PLAN FOR 
select count(*) as count ,TO_CHAR(fin.fin_date, 'DD-MON-YY') as dateAndTime from  (select * from rec where rec_status='RECEIVED' and fin_date>sysdate-8) fin group by TO_CHAR(fin.fin_date, 'DD-MON-YY')

在任何oracle sql客户端和read the doc中，了解为什么它太慢以及如何提高性能。(盲目猜测:在fin_date上建立索引)

Answer 2

为什么不使用一个查询而不是两个查询呢？

select count(*) as count ,TO_CHAR(fin.fin_date, 'DD-MON-YY') as dateAndTime 
from rec 
where rec_status='RECEIVED' and fin_date>sysdate-8
group by TO_CHAR(fin.fin_date, 'DD-MON-YY');