martes, 6 de julio de 2010

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

1 comentario:

Pepe dijo...

esta muy bueno el blog, pero porq no publicas una basesita de ejemplo para correr ahi tus scripts, asi no tendrias q crear una tabla para cada ejemplo...

gracias.