Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I remember that there is a performance issue with SQLs that use "<" against "<=" because of statistics and/or histograms and/or the data itself, but I dont remember exactly the issue.
I know this is very abstract question, but still if you can assists with what exactly the issue here
For example:
select id
from table
where year < 2010
Against
select id
from table
where year <= 2009
Yes, there is a difference, and the issue is that the estimated selectivity (thus cardinality) will be different in the 2 cases.
For a simple example, lets say the year column has years in it from 2000 (low_value) to 2014 (high_value), so there are 15 distinct values (num_distinct), they are evenly distributed, 1000 rows for each, 15000 rows (num_rows) in total in your table.
In the first case, where year < 2010 (limit = 2010), the estimated selectivity will be:
(limit - low_value) / (high_value - low_value) = (2010 - 2000) / (2014 -2000) = 10 / 14
Cardinality = 10/14 * 15000 = 10714
For the second case, where year <= 2009 (limit = 2009), selectivity is calculated as:
(limit - low_value) / (high_value - low_value) + 1/num_distinct = (2009 - 2000) / (2014 - 2000) + 1/15 = 9/14 + 1/15
Cardinality = (9/14 + 1/15) * 15000 = 10642
Not a big difference, but it can still affect the optimizer so that it choses another plan.
Above is explained in detail by Jonathan Lewis in his book "Cost-Based Oracle Fundamentals".
