By default Oracle Data Integrator (ODI) 11g uses the “old-style” join syntax when joining tables. While many Oracle developers still prefer that style of join, I for one find ANSI joins to be far cleaner and easier to read.
Oracle started supporting ANSI joins in 2001, so it is highly unlikely using these will cause your database to crumble to the ground. While there are ramblings that ANSI joins are now better supported by Oracle’s 12c optimizer, I cannot find concrete evidence either way to support choosing one over the other for performance reasons.
If you want ODI to use ANSI joins instead of the old style,
go to the “Quick Edit” tab in your ODI interface and expand out the “Joins” section. You will see an “Ordered” checkbox to the right of each join. Select this to use ANSI style joins. You will also need to make sure your table joins are ordered correctly. This can be done by using the “Order” column next to the “Ordered” checkbox. You cannot use a mix of join types, so either select or deselect all of the checkboxes.
Here are the differences between the two syntaxes:
Old-Style Join Syntax
- Relationships are defined in the WHERE clause
- Outer joins are represented by (+)
SELECT c.customer_name,
d.dept_no,
p.position_name
FROM customer c, department d, position p
WHERE c.dept_no = d.dept_no
AND c.position_code=p.position_code (+)
ANSI Join Syntax
- Relationships are defined using the ON clause
- All joins are named in statement allowing for better readability
SELECT c.customer_name,
d.dept_no,
p.position_name
FROM customer c
INNER JOIN department d
ON c.dept_no = d.dept_no
LEFT OUT JOIN position p
ON c.position_code=p.position_code
In ODI 12c, you can use ANSI joins by finding and selecting the “Generate ANSI Syntax” checkbox. Yes, Oracle has improved its choice of name in 12c.
Hope this helps, Nic!
We run regular business intelligence courses in both Wellington and Auckland. Find out more here.
