I ran into a really ugly SQL statement last week. It was the most expensive statement running on the system for the last several weeks. On top of the fact that the statement ran for hours, it also had a number of “issues”. The statement had the following characteristics:
- Several DISTINCT Operators – these tend to show up in statements where developers have left off a join condition
- ANSI Join Syntax – generally Oracle converts this syntax back to Oracle join syntax under the covers. I realize this is standard SQL syntax, but it still makes me nervous due to previous experience with problems caused by this conversion process.
- Functions Used in WHERE Clauses (DECODE and NVL in particular) – can disable indexes
- UNION – forces a sort of the union’ed result sets so that it can eliminate duplicates (which is often not necessary). Depending on how the statement is coded, UNIONs can also sometimes result in multiple accesses of the same objects (i.e. if the developer has used the UNION in the place of a more robust where clause).
- Numerous != and <> Comparison Operators – Not necessarily a problem but often can be coded in a more efficient manner
- Several OR’s – OR’s are tricky. I get suspicious when there are numerous other problems in a statement as well.
I’ll refrain from publishing the actual statement to protect the guilty, but one of the most interesting bits of the statement looked something like this:
... WHERE a.col1 = DECODE ('XYZ', '*', a.col1 ,'XYZ') ...
The processing of the where clause can be roughly translated to the following psuedo code:
if 'XYZ' = '*' then x = a.col1 else x = 'XYZ' end if ... WHERE a.col1 = x ...
Since the literal ‘XYZ’ will never be equal to the literal ‘*’, the value returned by this DECODE will always be ‘XYZ’. Therefore the statement could have been written much more simply as:
... WHERE a.col1 = 'XYZ' ...
So this is an extremely strange way to write the statement and clouds the intention considerably, but does it hurt performance? Well actually no. The optimizer is smart enough to know that this DECODE will always result in the same literal value and so it appears that Oracle does not run the DECODE statement for each row. Here’s a quick example from a 10.2.0.4 database. (note that I used dplan.sql and fss.sql in this code snippet)