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 >= <=
