NATURAL JOIN antinatural

tag:blogger.com,1999:blog-6230959840389481677.post-5654127756760127702008-03-26T11:36:00.000-07:002008-03-31T15:41:18.759-07:00NATURAL JOIN antinaturalEl 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, lo cierto es que el natural join introduce algunos riesgos que veremos más adelante. Un caso de prueba Tengo tablas que describen clientes, órdenes, y libros. Deseo realizar una consulta joineandolas a todas. <pre>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 </pre>A considerar:<ul><li>Todas las tablas tienen primary key, foreign keys, índices y estadísticas.</li><li>El JOIN que deseo hacer puede verse mirando las cuatro tablas desde arriba hacia abajo.</li><li>Las columnas con igual color determinan las llaves del JOIN.</li><li>La consulta que quiero realizar es --> Cuáles son los libros que ordenó JAKE LUCAS?</li></ul>Resultado con INNER JOIN <pre>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.</pre>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 <pre>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.</pre>Retorna 64 filas, algo inquietante. Se estará realizando un producto cartesiano? Veamos el plan de ejecución: <pre>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)</pre>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! <pre>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.</pre>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. Habrá que esperar que salga el próximo release de 11g? Yo no me molestaría. 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 mismo SQL, el NATURAL JOIN es uno de esos accesorios inútiles, de los cuales podemos prescindir totalmente (de hecho deberíamos). 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.lferhttp://www.blogger.com/profile/00618392085702183279noreply@blogger.com