miércoles, 26 de marzo de 2008

NATURAL JOIN antinatural

El NATURAL JOIN en Oracle tiene un bug. Las versiones 9i y 10g retornan extraños productos cartesianos cuando ejecutamos joins naturales con varias tablas a la vez.

El join natural
Un natural join toma las columnas de igual nombre entre dos tablas y las utiliza para realizar un join. ¿Cuál es el beneficio? No hay que nombrar las columnas en el join.
Si bien puede sonar fantástico el ahorrarnos de escribir las columnas en el JOIN, en Oracle el natural join produce resultados inesperados que veremos más adelante.

Un caso de prueba
Tengo tablas que describen clientes, órdenes, y libros. Deseo realizar una consulta joineandolas a todas.

SQL> desc customers
Name Type Nullable Default Comments
--------- ------------ -------- ------- --------
CUSTOMER# NUMBER(4)
LASTNAME VARCHAR2(10) Y
FIRSTNAME VARCHAR2(10) Y
ADDRESS VARCHAR2(20) Y



SQL> desc orders
Name Type Nullable Default Comments
--------- --------- -------- ------- --------
ORDER# NUMBER(4)
CUSTOMER# NUMBER(4) Y
ORDERDATE DATE Y



SQL> desc orderitems
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
ORDER# NUMBER(4)
ISBN VARCHAR2(10)
QUANTITY NUMBER(3) Y



SQL> desc books
Name Type Nullable Default Comments
------- ------------ -------- ------- --------
ISBN VARCHAR2(10)
TITLE VARCHAR2(30) Y
PUBDATE DATE Y
COST NUMBER(5,2) Y

A considerar:

  • Todas las tablas tienen primary key, foreign keys, índices y estadísticas.
  • El JOIN que deseo hacer puede verse mirando las cuatro tablas desde arriba hacia abajo.
  • Las columnas con igual color determinan las llaves del JOIN.
  • La consulta que quiero realizar es --> Cuáles son los libros que ordenó JAKE LUCAS?


Resultado con INNER JOIN

SQL> SELECT isbn, title
2 FROM ((customers INNER JOIN orders USING (customer#))
3 INNER JOIN orderitems USING (order#))
4 INNER JOIN books USING (isbn)
5 WHERE firstname='JAKE' AND lastname='LUCAS';


ISBN TITLE
---------- ------------------------------
2491748320 PAINLESS CHILD-REARING
9247381001 HOW TO MANAGE THE MANAGER
2491748320 PAINLESS CHILD-REARING


3 rows selected.

Es el resultado esperado. El cliente JAKE LUCAS ordenó los 3 libros que aparecen. ¿Qué sucede si ejecutamos la misma consulta pero con NATURAL JOIN?

Resultado con NATURAL JOIN

SQL> SELECT isbn, title
2 FROM customers NATURAL JOIN orders
3 NATURAL JOIN orderitems
4 NATURAL JOIN books
5 WHERE firstname='JAKE' AND lastname='LUCAS';


ISBN TITLE
---------- ------------------------------
1059831198 BODYBUILD IN 10 MINUTES A DAY
0401140733 REVENGE OF MICKEY
0401140733 REVENGE OF MICKEY
0401140733 REVENGE OF MICKEY
0401140733 REVENGE OF MICKEY
8843172113 DATABASE IMPLEMENTATION
8843172113 DATABASE IMPLEMENTATION
8843172113 DATABASE IMPLEMENTATION
3437212490 COOKING WITH MUSHROOMS
3437212490 COOKING WITH MUSHROOMS
3437212490 COOKING WITH MUSHROOMS
3957136468 HOLY GRAIL OF ORACLE
1915762492 HANDCRANKED COMPUTERS
...


64 rows selected.

Retorna 64 filas, algo inquietante. Se estará realizando un producto cartesiano? Veamos el plan de ejecución:

SQL> set autotrace traceonly explain


SQL> SELECT isbn, title
2 FROM customers NATURAL JOIN orders
3 NATURAL JOIN orderitems
4 NATURAL JOIN books
5 WHERE firstname='JAKE' AND lastname='LUCAS';



Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=3 Bytes=204)
1 0 NESTED LOOPS (Cost=9 Card=3 Bytes=204)
2 1 MERGE JOIN (CARTESIAN) (Cost=9 Card=1 Bytes=57)
3 2 MERGE JOIN (Cost=6 Card=1 Bytes=22)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMERS' (TABLE) (Cost=2 Card=1 Bytes=18)
5 4 INDEX (FULL SCAN) OF 'SYS_C00138991' (INDEX (UNIQUE)) (Cost=1 Card=20)
6 3 SORT (JOIN) (Cost=4 Card=21 Bytes=84)
7 6 TABLE ACCESS (FULL) OF 'ORDERS' (TABLE) (Cost=3 Card=21 Bytes=84)
8 2 BUFFER (SORT) (Cost=6 Card=14 Bytes=490)
9 8 TABLE ACCESS (FULL) OF 'BOOKS' (TABLE) (Cost=3 Card=14 Bytes=490)
10 1 INDEX (RANGE SCAN) OF 'IX_BOOKS' (INDEX) (Cost=0 Card=2 Bytes=22)
Efectivamente, en la línea 2 del plan aparece un MERGE JOIN (CARTESIAN) lo cual nos indica que un producto cartesiano se llevó a cabo. Esto no debería suceder, ya que las columnas para vincular las tablas con NATURAL JOIN existen y fueron verificadas en la primera consulta, usando la cláusula USING del INNER JOIN.

Resultado con NATURAL JOIN variando las columnas del SELECT
Si bien lo anterior no era normal, es aún más desconcertante lo que obtenemos si variamos las columnas del select: ¡La cantidad de registros también varía!

SQL> SELECT #order
2 FROM customers NATURAL JOIN orders
3 NATURAL JOIN orderitems
4 NATURAL JOIN books
5 WHERE firstname='JAKE' AND lastname='LUCAS';



42 rows selected.


SQL> SELECT isbn
2 FROM customers NATURAL JOIN orders
3 NATURAL JOIN orderitems
4 NATURAL JOIN books
5 WHERE firstname='JAKE' AND lastname='LUCAS';



64 rows selected.


SQL> SELECT #customer
2 FROM customers NATURAL JOIN orders
3 NATURAL JOIN orderitems
4 NATURAL JOIN books
5 WHERE firstname='JAKE' AND lastname='LUCAS';



896 rows selected.


SQL> SELECT *
2 FROM customers NATURAL JOIN orders
3 NATURAL JOIN orderitems
4 NATURAL JOIN books
5 WHERE firstname='JAKE' AND lastname='LUCAS';


3 rows selected.

Unicamente cuando seleccionamos todas las columnas (*) obtenemos el resultado correcto. ¡Esto debería desalentar a cualquiera a usar NATURAL JOIN!

Bug reconocido
La verdad es, que este bug fue reconocido por Oracle en Metalink, el soporte oficial. Lo que se informa es, que este bug se cree que será eliminado a partir de la versión 11.2. Pero eso deja de ser un problema si eliminamos hoy el uso de natural join, definitivamente.

Nunca usar NATURAL JOIN
El NATURAL JOIN es una sentencia que está en Oracle desde la versión 9i en un esfuerzo por cumplir con el estándar ANSI SQL. Dentro del propio mundo del SQL, el NATURAL JOIN es uno accesorio inútil como pocos, del cual podemos prescindir totalmente.

Más allá de que no funcione correctamente en este RDBMS, imaginemos que si lo hace y que en Oracle 11g R2 tenemos este bug solucionado: el NATURAL JOIN funciona de maravillas.
Ahora, que sucede con mi procedimiento si el día de mañana agregan una columna de nombre quantity a mi tabla books?
Como orderitems ya tiene una columna quantity, se van a retornar resultados inesperados, ya que la tablas se van a combinar por isbn y quantity (lo cual no tiene sentido). Un cambio leve en las estructuras modifica el comportamiento de mi JOIN, y en este caso no se trata de un bug sino que sería bastante lógico que lo hiciera.

Usar NATURAL JOIN también nos quita claridad en nuestro código: cada vez que lo encontramos en una consulta compleja perdemos tiempo buscando en las estructuras cuáles son las columnas que coinciden.

Lo mejor en todos los casos es usar INNER JOIN o la notación original de Oracle por medio de comparadores en el where. Con ellas el programador explicita cuáles son las columnas que deben entrar en juego, sin dejar nada librado al azar.

En conclusión, usar NATURAL JOIN es totalmente desaconsejado en SQL, ya que nuestras consultas dependen del nombrado de columnas en las tablas relacionadas. Un cambio insignificante como agregar una columna a una tabla, podría hacer dejar de funcionar mi aplicación.

No hay comentarios: