1.实例分析如下:
select count(0) from info_terminal_user a
where trunc(a.REG_DATE,'dd') = sysdate;
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3322 Card=1 Bytes=7)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'INFO_TERMINAL_USER' (Cost=3322 C
ard=33176 Bytes=232232)
select count(0) from info_terminal_user a
where a.REG_DATE > trunc(sysdate,'dd') and a.reg_date<trunc(sysdate+1,'dd')
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3322 Card=1 Bytes=7)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'INFO_TERMINAL_USER' (Cost=3322
Card=1160 Bytes=8120)
2.总结:日期条件出现的时候尽量不要在日期字段上面做运算,把运算转移到字段的外边;
select count(0) from info_terminal_user a
where trunc(a.REG_DATE,'dd') = sysdate;
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3322 Card=1 Bytes=7)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'INFO_TERMINAL_USER' (Cost=3322 C
ard=33176 Bytes=232232)
select count(0) from info_terminal_user a
where a.REG_DATE > trunc(sysdate,'dd') and a.reg_date<trunc(sysdate+1,'dd')
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3322 Card=1 Bytes=7)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'INFO_TERMINAL_USER' (Cost=3322
Card=1160 Bytes=8120)
2.总结:日期条件出现的时候尽量不要在日期字段上面做运算,把运算转移到字段的外边;