Comparison of Oracle Database date filtering methods and oracle Performance
When developing SQL statements, filtering date segments is a common problem. How can we efficiently filter out date segments? This article demonstrates through experiments:
Method 1: Use to_char to convert a date to a string for comparison
To_char (cr. contractenddate, 'yyyy-MM-DD ')> = '2017-11-13'
And to_char (cr. contractenddate, 'yyyy-MM-DD ') <= '2017-11-19'
Time consumption: 0.171 s
Method 2: convert a string to a date through to_date for comparison
Cr. contractenddate> = to_date ('2017-11-13 ', 'yyyy-MM-DD ')
And cr. contractenddate <= to_date ('1970-11-19 ', 'yyyy-MM-DD ')
Time consumption: 0.093 s
Method 3: comparison using the database keyword between and
Cr. contractenddate
Between to_date ('2017-11-13 ', 'yyyy-MM-DD') and to_date ('2017-11-19 ', 'yyyy-MM-DD ')
Time consumption: 0.078 s
Summary: method 1 is time-consuming because each character in the string needs to be compared one by one;
Method 2 time consumption is reflected in> = and <=;
Recommended method 3