Comparison of Oracle Database date filtering methods and oracle Performance

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