SQL Certified Expert Exam
I’ve been working with one of my lab tutors to have him take the 1Z0-047 Oracle Database SQL Expert test. He checked out the online practice exam, and found a couple interesting questions and new syntax. At least, it was new to me.
Naturally, I checked it out. I’ve also added it to my online tutorial for the class. Perhaps I’m a creature of habit but a range non-equijion is always a filtered cross product logically. Certainly, the explain plans indicate that this new syntax has zero performance change over the other forms.
I once used the comma-delimited tables (like everybody else), but now I try to always use the newer CROSS JOIN
syntax. In both cases the range join is put in the WHERE
clause. The new syntax uses an INNER JOIN
and an ON
clause to hold the range match. Examples of all are below.
Comma-delimited Filtered Cross Join
1 2 3 4 5 | SELECT c.month_short_name , t.transaction_amount FROM calendar_join c, transaction_join t WHERE t.transaction_date BETWEEN c.start_date AND c.end_date ORDER BY EXTRACT(MONTH FROM t.transaction_date); |
Filtered CROSS JOIN
1 2 3 4 5 | SELECT c.month_short_name , t.transaction_amount FROM calendar_join c CROSS JOIN transaction_join t WHERE t.transaction_date BETWEEN c.start_date AND c.end_date ORDER BY EXTRACT(MONTH FROM t.transaction_date); |
Range filtered INNER JOIN
1 2 3 4 5 | SELECT c.month_short_name , t.transaction_amount FROM calendar_join c INNER JOIN transaction_join t ON (t.transaction_date BETWEEN c.start_date AND c.end_date) ORDER BY EXTRACT(MONTH FROM t.transaction_date); |
Without an INDEX
on the start and end date of the CALENDAR_JOIN
table, the Oracle explain plan for all three queries is:
1 2 3 4 5 6 7 8 9 10 | Query Plan ---------------------------------------------- SELECT STATEMENT Cost = 9 SORT ORDER BY MERGE JOIN SORT JOIN TABLE ACCESS FULL TRANSACTION_JOIN FILTER SORT JOIN TABLE ACCESS FULL CALENDAR_JOIN |
Naturally, an INDEX
on the START_DATE
and END_DATE
columns improves performance. The results again for all three are the same.
1 2 3 4 5 6 7 8 | Query Plan ---------------------------------------------- SELECT STATEMENT Cost = 6 SORT ORDER BY TABLE ACCESS BY INDEX ROWID CALENDAR_JOIN NESTED LOOPS TABLE ACCESS FULL TRANSACTION_JOIN INDEX RANGE SCAN DATE_RANGE |
Unless I’m missing something, it looks like its only a matter of style. However, make sure you know that new one because it appears that it’s on the OCP exam. 😉
Comments are always welcome …