Why Choosing ANSI JOIN is an Operational Issue

From an operational perspective, I argue that the use of ANSI syntax for JOINs is superior to that of Oracle’s native syntax.

We recently experienced a serious operational issue in one of our production environments because of the reliance on Oracle’s native join syntax.

A developer wrote a query similar to the following:

SELECT * FROM t1, t2
WHERE t1.col1 = t2.col2;

This tested satisfactorially in the QAT environment. Good practice, so far.

However when the developer transferred the code to the production environment, only the first line was transferred. This resulted in a Cartesian Join. The intermediate result set filled up the temporary tablespace.

Unfortunately, we had embraced BIGFILE for temporary tablespace and did not specify a maximum size for the tablespace. Thus, all of the available disk space was consumed. We had to request an emergency allocation of disk to forestall any possible stalls due to other tablespaces failing to auto-extend.

My argument is that, if the developer has used ANSI JOIN syntax, his query would have failed with a syntax error.

This advice will fall upon the deaf ears of developers because they consider ANSI JOIN syntax useful only for newbies. Anyone who can decipher Oracle’s native join syntax for a fourteen table join deserves my utmost respect.

I know that Oracle’s implementation of ANSI JOINs has been racked by problems with wrong results and slow performance, but I am looking at this issue from an operational perspective.

My own preference is to always use the ANSI JOIN syntax as it forces me to think about JOIN conditions as I write the SQL code, and the syntax clearly highlights what is being joined, and how it is being joined.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s