查询数据按日期字段作筛选,导致索引失效的情况

查询数据按日期字段作筛选,导致索引失效的情况

1.使用sqoop查询前10天的数据增量,发现耗时很长,大概用了3分钟才查询出来
test_query "
 select count(1) as cnt
 from test.mandpay
 where to_char(updatetime,'YYYY-MM-DD')>to_char(sysdate-10,'yyyy-mm-dd')
"
 2024-12-31 17:51:34,743 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.

------------------------
| CNT                  |
------------------------
| 377075               |
------------------------

date
Tue Dec 31 17:54:48 CST 2024

2.使用sqoop查询前10天的数据增量,几秒钟就查询出来了
test_query "
 select count(1) as cnt
 from test.mandpay
 where updatetime>=TRUNC(SYSDATE-10) 
"
2024-12-31 17:55:44,366 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
------------------------
| CNT                  |
------------------------
| 406545               |
------------------------

date
Tue Dec 31 17:55:50 CST 2024

3.问题原因
updatetime 该字段在业务系统是索引字段,第一种方式对索引字段做了to_char操作,导致索引失效
改成用第二种方式就用上了索引,明显地提升了查询效率