查询数据按日期字段作筛选,导致索引失效的情况
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操作,导致索引失效
改成用第二种方式就用上了索引,明显地提升了查询效率