Take the 2-minute tour ×
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's 100% free, no registration required.

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
share|improve this question
4  
I would be very surprised if you'd see a difference there. Check the execution plan and you'll see if there is (autotrace mode in SQL*Plus can also help you identify any differences) –  a_horse_with_no_name Mar 3 at 14:48

1 Answer 1

up vote 3 down vote accepted

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".

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.