Diferencias entre BETWEEN y >= <=

Muchas personas prefieren utilizar el BETWEEN en vez de >= <= y otras personas prefieren usar lo contrario porque suponen que hay alguna diferencia a nivel de performance. Esto es un mito muy antiguo. La realidad es que no existen diferencias.

BETWEEN es una "sinónimo" de >= <=

Voy a demostrar esto con una sencilla prueba:

SQL> CREATE TABLE test AS
  2  SELECT level id, 'nombre_'||level nombre
  3  FROM dual
  4  CONNECT BY level <= 100;

Table created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST', estimate_percent => 100, method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> DESC test

 Name          Null?    Type
 ------------- -------- --------------
 ID                     NUMBER
 NOMBRE                 VARCHAR2(47)

SQL> SELECT nombre
  2  FROM test
  3  WHERE id BETWEEN 20 AND 25;

NOMBRE
---------
nombre_20
nombre_21
nombre_22
nombre_23
nombre_24
nombre_25

6 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Plan hash value: 1357081020

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| TEST |      1 |      7 |      6 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("ID"<=25 AND "ID">=20))

Como podemos ver en la sección "Predicate Information", el optimizador reemplazó la sentencia BETWEEN por "ID"<=25 AND "ID">=20.
También podemos ver, que leyó 4 bloques de datos para recuperar los 6 registros mediante un acceso FULL SCAN.

Ahora, probemos colocando el >= <= en la consulta ...

SQL> SELECT nombre
  2  FROM test
  3  WHERE id >= 20 AND id <= 25;

NOMBRE
---------
nombre_20
nombre_21
nombre_22
nombre_23
nombre_24
nombre_25

6 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Plan hash value: 1357081020

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| TEST |      1 |      7 |      6 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("ID"<=25 AND "ID">=20))   

Acá volvemos a ver en la a sección "Predicate Information" que el optimizador no realizó ningún reemplazo.
Seguimos leyendo 4 bloques de datos para recuperar los 6 registros mediante un acceso FULL SCAN.

Una cosa muy importante a destacar entre estas 2 consultas es que el hash value de ambas es el mismo (1357081020). Esto nos indica que la utilización de BETWEEN o >= <= es transparente para el optimizador ya que siempre va a utilizar >= <= para resolver la consulta.

Qué sucede si creamos un índice en la tabla? Obtendremos alguna diferencia?

SQL> CREATE UNIQUE INDEX test_uq ON test(id, nombre);

Index created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST', estimate_percent => 100, method_opt => 'for all columns size 1', cascade => true);

PL/SQL procedure successfully completed.

SQL> SELECT nombre
  2  FROM test
  3  WHERE id BETWEEN 20 AND 25;

NOMBRE
---------
nombre_20
nombre_21
nombre_22
nombre_23
nombre_24
nombre_25

6 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Plan hash value: 1087767317

--------------------------------------------------------------------------------------
| Id  | Operation        | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|*  1 |  INDEX RANGE SCAN| TEST_UQ |      1 |      7 |      6 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ID">=20 AND "ID"<=25)

Vemos que se volvió a reemplazar el BETWEEN por >= <= y ahora como estamos accediendo por índice, leemos solamente 2 bloques de datos.

Qué sucederá si modificamos nuevamente la consulta?

SQL> SELECT nombre
  2  FROM test
  3  WHERE id >= 20 AND id <= 25;

NOMBRE
---------
nombre_20
nombre_21
nombre_22
nombre_23
nombre_24
nombre_25

6 rows selected.

sys@orcl> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Plan hash value: 1087767317

--------------------------------------------------------------------------------------
| Id  | Operation        | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|*  1 |  INDEX RANGE SCAN| TEST_UQ |      1 |      7 |      6 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ID">=20 AND "ID"<=25)

Obtuvimos exactamente el mismo resultado que con el BETWEEN.
El hash value entre las 2 consultas sigue siendo el mismo (1087767317).

En esta prueba, colocamos el campo numérico ID en el WHERE pero qué sucedería si utilizamos un campo caracter?

SQL> DROP TABLE test;

Table dropped.

SQL> CREATE TABLE test AS
  2  SELECT to_char(level) id, 'nombre_'||level nombre
  3  FROM dual
  4  CONNECT BY level <= 100;

Table created.

SQL> DESC test

 Name          Null?    Type
 ------------- -------- --------------
 ID                     VARCHAR2(40)
 NOMBRE                 VARCHAR2(47)

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST', estimate_percent => 100, method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> SELECT nombre
  2  FROM test
  3  WHERE id BETWEEN 20 AND 25;

NOMBRE
---------
nombre_20
nombre_21
nombre_22
nombre_23
nombre_24
nombre_25

6 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Plan hash value: 1357081020

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| TEST |      1 |      1 |      6 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((TO_NUMBER("ID")>=20 AND TO_NUMBER("ID")<=25))
   
SQL> SELECT nombre
  2  FROM test
  3  WHERE id >= 20 AND id <= 25;

NOMBRE
-----------------------------------------------
nombre_20
nombre_21
nombre_22
nombre_23
nombre_24
nombre_25

6 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Plan hash value: 1357081020

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| TEST |      1 |      1 |      6 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((TO_NUMBER("ID")>=20 AND TO_NUMBER("ID")<=25))

Como observamos, ambas consultas siguen siendo idénticas.


Estas pruebas nos indican que no existe diferencia alguna entre la utilización del BETWEEN y el >= <=