Oracle (Old?) Joins - A tool/script for conversion?

Question

I have been porting oracle selects, and I have been running across a lot of queries like so:

SELECT e.last_name,
       d.department_name
  FROM employees e,
      departments d
WHERE e.department_id(+) = d.department_id;

...and:

SELECT last_name, 
       d.department_id
  FROM employees e, 
       departments d
 WHERE e.department_id = d.department_id(+);

Are there any guides/tutorials for converting all of the variants of the (+) syntax? What is that syntax even called (so I can scour google)?

Even better.. Is there a tool/script that will do this conversion for me (Preferred Free)? An optimizer of some sort? I have around 500 of these queries to port..

When was this standard phased out? Any info is appreciated.

Answer 1

Google "Oracle join syntax". the (+) is used for different flavours of outer joins. I think the first one you showed is a Left Outer Join, and the second one is a Right Outer Join. I haven't seen this notation for quite a while, so I could be a little off, but hopefully, this gives you enough info to hit Google and get the right answer.

UPDATE:

So you wants a tool to do it for you? I have heard that SwisSQL can do something like this, but if most of the queries are simple enough you can probably write a little script that does it for you. OMG Ponies answer nicely shows the pattern for converting from old to new syntax.

Answer 2

For Oracle 10g

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm#i2054012

You can find the other versions of oracle manuals online if your version is different, though the join syntax is probably not different.

Answer 3

The (+) is Oracle specific pre-ANSI-92 OUTER JOIN syntax, because ANSI-89 syntax doesn't provide syntax for OUTER JOIN support.

Whether it is RIGHT or LEFT is determined by which table & column reference the notation is attached to. If it is specified next to a column associated with the first table in the FROM clause - it's a RIGHT join. Otherwise, it's a LEFT join. This a good reference for anyone needing to know the difference between JOINs.

First query re-written using ANSI-92 syntax:

    SELECT e.lastname,
           d.department_name
      FROM EMPLOYEES e
RIGHT JOIN DEPARTMENTS d ON d.departmentid = e.departmentid

Second query re-written using ANSI-92 syntax:

   SELECT e.lastname,
          d.department_name
     FROM EMPLOYEES e
LEFT JOIN DEPARTMENTS d ON d.departmentid = e.departmentid

Answer 4

I seem to remember this syntax going away in the transition from Oracle 8i to 9i -- I think we had a toad plugin that ended up converting everything for us just so we didn't have to waste time going through every query

Answer 5

This can get quite complicated as the WHERE clause in even simple situations such as

WHERE e.id = d.manager_id(+) OR e.id = d.contact_id(+)

will translate to UNION or subselect query.

If you like python you might take a look at sqlparse, it looks promising and you might get it to do what you need plus some reformatting of the SQL code. It would easily work directly on the source. You'll have to tell it what to do but it does relieve you of writing the boring parsing part.

Answer 6

I don't know of a tool to do the conversion automatically, but even if there were, you would want to review its changes one a case by case basis anyway. Therefore, I don't think a tool would save you much time.

http://www.dba-oracle.com/oracle_news/2004_2_19_rittman.htm says:

There's no performance benefit or hit by using ANSI joins rather than traditional joins, but by using ANSI joins, your queries are more portable between DBMS platforms, and they're a bit easier to read. In the end, though, it's down to personal preference and whilst there's advantages to the ANSI standard, there's no need to switch if you don't want to.

Also you don't have to use one style or the other everywhere. You can convert your code one query at a time, with some assurance that they will all keep working. I would leave the queries as they are, and resolve to use ANSI SQL-92 syntax in new code.

Answer 7

SELECT obj_obj_hirchy_id, obj_host_ip, AVG (HI_MONITORED_VALUE) AS HI_MONITORED_VALUE FROM object_obj_hierarchy INNER JOIN object_configuration ON OBJECT_CONFIGURATION.OBJ_CONFIG_ID = OBJECT_OBJ_HIERARCHY.OBJ_OBJ_HIRCHY_OBJ_CNFG_ID AND object_configuration.obj_config_record_status = 'A' AND object_configuration.obj_config_obj_mngt_type_id IN (SELECT mgmnt_type_id FROM info_parameter WHERE info_parameter.record_status = 'A' AND INFO_EVENT_TYPE = 'cpuUtilization' AND info_parameter.parameter_type = 'default') INNER JOIN objects ON objects.obj_id = object_obj_hierarchy.obj_obj_hirchy_object_id AND objects.obj_cust_id = 1 LEFT JOIN historical_info ON object_obj_hierarchy.obj_obj_hirchy_id = historical_info.HI_OBJ_OBJHIERARCHY_ID AND historical_info.hi_timestamp_created >= TO_DATE ('26-Apr-10') AND historical_info.hi_timestamp_created < SYSDATE AND historical_info.hi_parameter_oid IN (SELECT mib_entries.mib_entries_oid FROM mib_entries INNER JOIN info_parameter ON mib_entries.mib_entries_id = info_parameter.mib_entries_id AND info_parameter.record_status = 'A' AND INFO_EVENT_TYPE = 'cpuUtilization' AND info_parameter.parameter_type = 'default' WHERE mib_entries.MIB_ENTRIES_RECORD_STATUS = 'A') WHERE object_obj_hierarchy.obj_obj_hirchy_record_status = 'A' GROUP BY objects.obj_host_ip, obj_obj_hirchy_id ORDER BY NVL (HI_MONITORED_VALUE, 0) DESC;

can any one convert this query to oracle proprietary sql?

I am really having a hard time converting this.

there is a performance issue so i need to convert it.