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.

Possibly related:

Tagged , | Post a Comment