msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Format your SQL the easy way

Let’s face it, SQL code is much easier and faster to read and understand when it is formatted, especially when it is a long and complicated SQL statement you are dealing with. Unformatted SQL can be a pain and can take a long time to understand. To feel the pain, take this example:

SYS@XE> select sql_text
  2  from v$sql
  3  where length(sql_text) between 700 and 800
  4  and rownum = 1;

SQL_TEXT
--------------------------------------------------
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.t
ype#,i.flags,i.property,i.pctfree$,i.initrans,i.ma
xtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.db
lkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i
.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.row
cnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,n
vl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),nvl(
i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,dec
ode(i.pctthres$,null,null,mod(trunc(i.pctthres$/25
6),256)),ist.cachedblk,ist.cachehit,ist.logicalrea
d from ind$ i, ind_stats$ ist, (select enabled, mi
n(cols) unicols,min(to_number(bitand(defer,1))) de
ferrable#,min(to_number(bitand(defer,4))) valid# f
rom cdef$ where obj#=:1 and enabled > 1 group by e
nabled) c where i.obj#=c.enabled(+) and i.obj# = i
st.obj#(+) and i.bo#=:1 order by i.obj#

Ouch! Just by looking at the above select statement gives me headache. I could format it manually myself but that would give me more headache and waste my time. What to do? Fortunately there are tools that automatically format any SQL code. For example SQL Navigator and Toad have formatting features, but you have to pay for them. I have searched the Internet and found the following free alternatives:

  • SQLinForm. No download necessary. It uses a Java applet. Feature rich. That’s the one I like.
  • Online SQL Formatter. No download or Java applet necessary.
  • SQL Review. Requires a client download and installation.

So, using SQLinForm, the above SQL was formatted like this:

SELECT 
    i.obj#,
    i.ts#,
    i.file#,
    i.block#,
    i.intcols,
    i.type#,
    i.flags,
    i.property,
    i.pctfree$,
    i.initrans,
    i.maxtrans,
    i.blevel,
    i.leafcnt,
    i.distkey,
    i.lblkkey,
    i.dblkkey,
    i.clufac,
    i.cols,
    i.analyzetime,
    i.samplesize,
    i.dataobj#,
    nvl(i.degree,1),
    nvl(i.instances,1),
    i.rowcnt,
    mod(i.pctthres$,256),
    i.indmethod#,
    i.trunccnt,
    nvl(c.unicols,0),
    nvl(c.deferrable#+c.valid#,0),
    nvl(i.spare1,i.intcols),
    i.spare4,
    i.spare2,
    i.spare6,
    decode(i.pctthres$,null,null,mod(trunc(
    i.pctthres$/256),256)),
    ist.cachedblk,
    ist.cachehit,
    ist.logicalread 
FROM 
    ind$ i, 
    ind_stats$ ist, 
    ( SELECT 
        enabled, 
        min(cols) unicols,
        min(to_number(bitand(defer,1))) deferrable#,
        min(to_number(bitand(defer,4))) valid# 
    FROM 
        cdef$ 
    WHERE 
        obj#        = :1 
        AND enabled > 1 
    GROUP BY 
        enabled
    ) 
    c 
WHERE 
    i.obj#     = c.enabled(+) 
    AND i.obj# = ist.obj#(+) 
    AND i.bo#  = :1 
ORDER BY 
    i.obj#

Wow! It looks much, much better and readable now.

Note that I had to remove the hard line breaks from the unformatted SQL before formatting it.


Filed in Oracle, Tips on 12 Dec 05 | Tags: ,


Reader's Comments

  1. |

    Online Format SQL

    TrackBack From:http://www.blogjava.net/waterye/archive/2005/12/13/23633.html

  2. |

    Fantastic! As useful to DBA as programmers. Thank you!

  3. |

    [...] Eddie Awad has a nice blog about three apps to format your SQL code. Especially the online tools, SQLinForm en the Online SQL Formatter, are very nice. Both offer some options to select the SQL dialect and the output format (e.g. upper/lowercase) and language (SQL, VB, Java etc). The former, being a java applet, is more feature-rich than the latter. [...]

  4. |

    It drives me crazy the way people write SQL code. I think a lot of people let the tools write the code for them, then they don’t bother to clean it up and format it. I did an article on the subject a few years ago that may be helpful to some: http://www.communitymx.com/content/article.cfm?cid=C138F

  5. |

    The good thing about formatting your sql is that the Oracle parser will hash a lot of statements to the same value, which results in faster execution. An even better thing than correctly formatting your SQL is writing no SQL at all. This can be accomplished by encapsulating all the needed SQL into packages which the other programs use. I know this is not always possible, especially with ‘legacy’ code, but if you are writing new programs it’s always good to think about it.

  6. |

    Patrick, just formatting (not tuning) your SQL statement has no effect on how fast or slow the statement executes, unless you (or someone else) have a proof otherwise.

    Moreover, if you encapsulate all the needed SQL into packages, you still need to write SQL (in the package), don’t you?

  7. |

    I can’t believe I didn’t know those tools existed :-) Great!

    I prefer sqlReview as it allows for command line use though.

  8. |

    Formatting a SQL statement can have an impact on performance. If two statements are the same but formatted diferently, they will produce a different hash value. This means they won’t be able to share the same shared SQL area.

    The exception to the rule is when the paremeter CURSOR_SHARING is set to SIMILAR or FORCE. CURSOR_SHARING=FORCE was introduced in Oracle 8.1.6, whereas FORCE was introduced in version 9.

    See http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14211/memory.htm#i40017 for more info.

  9. |

    Just to be clear, when I commented above that “just formatting (not tuning) your SQL statement has no effect on how fast or slow the statement executes” I meant that, given the following:

    select * from employees where first_name = ‘Joe’

    Does formatting the above to:

    SELECT * FROM employees WHERE first_name = ‘Joe’

    Make it run faster or impact performance?

    It may or it may not. But adding an index on first_name will (if the optimizer chooses so).

    My point was to not to rely on formatting to “tune” your SQL.

    However, as Wilfred said, the two queries may be different as far as Oracle’s shared SQL area is concerned.

  10. |

    [...] I did not have time to play with every feature of this exciting new and free product, however, I did notice one feature: SQL formatting. What a coincidence! A few days ago, I blogged about a few SQL formatter tools. In Raptor, you can format your SQL by right clicking your mouse in the SQL Worksheet area and clicking on "Format SQL…", or by simply hitting Ctrl-B [...]

  11. |

    It seems unnecessary to remove the hard line breaks from the unformatted SQL before formatting it, this free online formatter

    http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl

    it has a option to remove linebreak before beautify automatically, it works fine.

  12. |

    [...] SQL formatters [...]

  13. |

    “Note that I had to remove the hard line breaks from the unformatted SQL before formatting it.”

    How?

  14. |

    Allen,

    for example, in the following:

    select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.t ype#,i.flags,i.property,i.pctfree$,i.initrans,i.ma

    I put my cursor at the beginning of the second line (just before the “y”) and hit the backspace on my keyboard. I did this for every line until the whole query is on one line before I applied the formatting. That’s what I meant by “remove the hard line breaks”.

  15. |

    [...] I pulled a large amount of SQL out of some existing code and wanted to have it formated nicely for me. I figured there had to be some type of pretty printer for SQL available outside of applications like TOAD. I have access to TOAD but it would have required a reboot so I figured I would ask google what to do. There seem to be a number of different formaters out there. I found a couple people talking about current options a list here and here. The following list is a summary of the options I found: [...]

  16. |

    Hi Eddie, thank you very much for this nice review of the sqlinform site.

    Normally, the hard-coded linebreaks should be removed automatically. Which operating system are you using?

    Regards GuidoMarcel

  17. |

    Hi Guido,

    You’ve done a great job with SQLinform. Thank you.

    I have just tried formatting the SQL text mentioned in the post above. It does indeed remove the hard line breaks and format the SQL beautifully, but it looks like the line breaks are being replaced with blanks. For example:

    mi
    n(cols)
    

    becomes

    mi n(cols)

    which of course is not valid SQL.

    By the way, I’m currently using Win XP Pro.

    Thanks again for this helpful utility.

  18. |

    To: Eddie Awad

    I used SQL Review from http://www.dbainfopower.com and it it formats broken words properly (I formatted output from v$sqltext )

  19. |

    Has anyone posted some open source Java to format SQL statements? I am building a tool that supports our commercial product, and I am extracting queries from the database and would like to format them. I’ve written something rudimentary, but I’d like to avoid the trouble of writing a good one.

  20. |

    Hi Eddie, finally i put into SQLinForm an option to remove linebreaks before formatting as you suggested. Thanks for this suggestion GuidoMarcel

  21. |

    Hey, from some time there is new formatting project on the web . You can find it on
    (beta). The goals are make multi platform sql formatter with editable database of “keywords”.

  22. |

    Interesting posts

    K Rosebrook

  23. |

    Useful links..