Performancewise between < and <= which is the best operator ? and why?

Question

In the following queries which query give better performance ? and how ?Kindly suggest me .

    SELECT *FROM emp e 
          WHERE e.hiredate<='02-JAN-1981';
     SELECT *FROM emp e 
         WHERE e.hiredate<'03-JAN-1981';

I don't remember for Oracle, but for Postgres those are not equivalent queries if *hiredate* is a timestamp. For timestamps, stringified dates are converted to midnight timestamps, so the two dates in your question are 23h 59m apart. That said, any true difference would be negligible. If the DB doesn't break down the SQL and convert the operator into the fastest machine code mechanism it can, then it could also depend on your data; the size of table and values exactly matching values could run against an index table. — vol7ron, Oct 01 '15 at 06:03
Not SQL, but similar [Is < faster than <=?](http://stackoverflow.com/q/12135518/995714) — phuclv, Oct 01 '15 at 06:17
Thanks, Lalit Kumar.Actually one of my senior team member said that , if we use < operator it gives better performance over <= . — MastanSky, Oct 01 '15 at 06:21
@MastanSky No problem. Show your senior team member my answer. And if he/she is not satisfied, ask to prove it with a working test case. — Lalit Kumar B, Oct 01 '15 at 06:47

Lalit Kumar B · Answer 1 · 2015-10-01T06:25:05.037

WHERE e.hiredate<='02-JAN-1981'

'02-JAN-1981' is a string, and not a DATE. You are forcing Oracle to do an implicit datatype conversion depending on your locale-specific NLS settings. Always use TO_DATE or ANSI date literal.

WHERE e.hiredate <= TO_DATE('02-JAN-1981', 'DD-MON-YYYY')

or,

WHERE e.hiredate <= DATE '1981-01-02'

Coming to your question regarding the difference in performance between the operators:

It is very obvious that if there are rows to be fetched matching the predicate for the value of hiredate, then Oracle has to do more work.

"<" will only fetch rows which are less than the provided date, however, "<=" will include all the rows which match the provided date.

In your case, both the queries will have same explain plan.

Open SQL*Plus, and do the following:

SQL> SET AUTOTRACE ON EXPLAIN

Query 1:

SQL> SELECT * FROM emp e WHERE e.hiredate<= DATE '1981-01-02';
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    37 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    37 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("E"."HIREDATE"<=TO_DATE(' 1981-01-02 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'))

Query 2:

SQL> SELECT * FROM emp e WHERE e.hiredate< DATE '1981-01-03';
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    37 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    37 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("E"."HIREDATE"<TO_DATE(' 1981-01-03 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'))

score 1 · Answer 2 · answered Oct 01 '15 at 05:30

1

No difference. See the explain plan. Cost is same for both.

answered Oct 01 '15 at 05:30

Utsav

7,914
2
17
38

Performancewise between < and <= which is the best operator ? and why?

2 Answers2