Oracle SQL Operator> = has better performance than>
I was asked one question in an interview.
Below two SQL will get the same result. but which one has better performance?
SELECT * FROM EMP WHERE DEPTNO >= 4
SELECT * FROM EMP WHERE DEPTNO > 3
The first answer. In the first SQL Database will look for DEPT = 4 directly when searching. it has better performance. In the second SQL Database will find DEPT = 3 and a scan forward row that is greater than 3.
I first heard this theory. I don't see any execution plan for the form of differences and statistics numbers.
Is there an official explanation for this?
I found that some Chinese sites use the same tips.
http://edm.ares.com.tw/dm/newsletter-2014-03-uPKI-OTP-newrelease/it-1.php
source to share
I believe it is a myth if a column has an index on that field. I tried using a primary key and both execution plans use index range scans which are quite fast on the primary key or unique index. The performance of the two queries is extremely close to each other, and both return the same cost, but the one with ">" was 0.3% higher in the estimated CPU price than "> =". I say BUSTED!
Also, when I say something works better, I would usually mean 10% +, if not more.
source to share
I'm not 100% sure, but if you created an index with a large PCTFREE and if you could force 3 in one leafblock and 4 in another leafblock. (This can be verified with ALTER DATABASE DUMP BLOCK
). And if you forced Oracle to use an index range scan.
Then perhaps you could see from the trace that one request was processing one more page than another.
source to share