For some time now Toad’s SQL Editor has offered several very basic SQL code refactoring capabilities. But to be honest those refactoring features did not see extensive use or adoption as they were so basic. Thus Toad 12.1, releasing Sept 19th 2013, will add several major SQL refactoring enhancements sure to be a huge success. In this blog I’m covering just the top four which I think will be the most useful. Let’s look at some of the clear and cool refactoring “home runs” coming soon to a Toad near you Big Smile

NOTE – All the examples below were accomplished via the Toad Editor’s Right-Hand-Mouse (i.e. RHM) menu and selecting Refactor. There are plans to create a new Refactoring toolbar for better exposure, but that’s not available yet in the current beta being used for this demo.

Refactor #1 – Convert to/from ANSI Join Syntax

Oracle has stated that going forward we all should be using the ANSI join syntax pretty much exclusively. Yes – I know that early versions had lots of bugs, but we’re many years past that point. However many of us remain more comfortable and quicker with the old syntax, plus we have tons of SQL code already written in the old syntax. Now Toad will make the Join syntax a moot issue – as you can switch back and forth between join syntax styles with the click of a button. Here’s an example of a six-way join converted from Oracle join syntax to ANSI – and it took all of just one second to convert. Remember – Toad offers converting in both directions, so to and from ANSI join syntax.

 

Refactor #2 – Remove Sub-Queries Using ANSI/Oracle Join Syntax

For many people reading and comprehending a nested sub-query is not as easy as an equivalent join. In fact in many cases the Oracle optimizer actually makes such conversions without the user ever knowing about it. Now Toad will understanding nested sub-queries a moot issue – as you can now convert to the equivalent join syntax with just the click of a button. Here’s a simple example of two nested sub-queries converted to ANSI join syntax – and it took all of just one second to convert. Remember – Toad offers converting nested sub-queries to both Oracle and ANSI join syntax.

 

Refactor #3 – Correct WHERE Clause Indention Level

This next example is very complex. But it represents real world problems routinely encountered when writing nested sub-queries. Notice that the original SQL is 18 lines with nested sub-queries up to three levels deep. Now note that the rewrite is just 10 lines – a 44% reduction – with sub-queries now only two levels deep. These coding mistakes fall into two categories – cut and paste errors/oversights and nested sub-query overload (i.e. not being able to see the trees from the forest). Let’s examine what Toad saw and corrected.

The lines marked as #1 are simple WHERE clause conditions nested at the wrong level (i.e. too deep). Those lines can be pushed out to the main WHERE clause since they’re only dependent on table “e1”. Furthermore they are duplicates and hence one can be dropped.

The lines marked as #2 are also simple WHERE clause conditions nested at the wrong level (i.e. too deep). Those lines can be pushed out to the second level WHERE clause since they’re only dependent on table “d1”. In this case there are no duplicates to remove.

The lines marked as #3 are WHERE clause sub-queries clauses nested at the wrong level (i.e. too deep). Those lines can be pushed out to the main WHERE clause since they’re only dependent on table “e1”. Furthermore they are duplicates and hence one can be dropped.

Would you have been able to spot and correct this kind of mistakes? Maybe – but it’s both a visual and mental challenge to be able to make such corrections. Now Toad does it auto-magically for you. The end result is SQL code that’s far easier to both read and maintain. Plus it also is far more efficient – look at the before vs. after explain plans shown here. Note – calling SQL Tuning tools such as Toad Xpert’s Auto or Advanced Optimize, or OEM’s Tuning Advisor would not find nor correct such coding mistakes! They operate on the premise of optimizing properly structured SQL – not correcting SQL coding mistakes.

Figure 1: Explain Plan Before Refactor

Figure 2: Explain Plan After Refactor

 

Refactor #4 – Convert DECODE Function to CASE Statement

Prior to ASNI SQL defining a CASE statement, Oracle had the DECODE function for handling “if then else” type logic. Sometime after ANSI defined the CASE statement, Oracle then added support for it. However many of us remain more comfortable and quicker using the old DECODE function, plus we have tons of SQL code already written using DECODE. Now Toad will make DECODE vs. CASE syntax a moot issue – as you can now easily convert all DECODE function calls to CASE statements with just the click of a button. No more counting parenthesis levels while trying to make the switch! Here’s a real-world DECODE to CASE statement conversion example from my recent blog on calculating IOPS. This may be a very long example, but it very clearly shows the power of auto-magic correction Big Smile

SELECT end_time,
      
ROUND(sr/inttime,3) sri,
      
ROUND(sw/inttime,3) swi,
      
ROUND((sr+sw)/inttime,3) tsi,
      
ROUND(sr/DECODE((sr+sw),0,1,(sr+sw))*100,3) srp,
      
ROUND(sw/DECODE((sr+sw),0,1,(sr+sw))*100,3) swp,
      
ROUND(lr/inttime,3) lri,
      
ROUND(lw/inttime,3) lwi,
      
ROUND((lr+lw)/inttime,3) tli,
      
ROUND(lr/DECODE((lr+lw),0,1,(lr+lw))*100,3) lrp,
      
ROUND(lw/DECODE((lr+lw),0,1,(lr+lw))*100,3) lwp,
      
ROUND((tbr/inttime)/1048576,3) tr,
      
ROUND((tbw/inttime)/1048576,3) tw,
      
ROUND(((tbr+tbw)/inttime)/1048576,3) tm
FROM
(
SELECT
beg.snap_id beg_id, end.snap_id end_id,
       beg
.begin_interval_time, beg.end_interval_time,
      
end.begin_interval_time begin_time, end.end_interval_time end_time,
      
(extract(day   from (end.end_interval_time - end.begin_interval_time))*86400)+
      
(extract(hour   from (end.end_interval_time - end.begin_interval_time))*3600)+
      
(extract(minute from (end.end_interval_time - end.begin_interval_time))*60)+
      
(extract(second from (end.end_interval_time - end.begin_interval_time))*01) inttime,
      
decode(end.startup_time,end.begin_interval_time,end.sr,(end.sr-beg.sr))   sr,
      
decode(end.startup_time,end.begin_interval_time,end.sw,(end.sw-beg.sw))   sw,
      
decode(end.startup_time,end.begin_interval_time,end.lr,(end.lr-beg.lr))   lr,
      
decode(end.startup_time,end.begin_interval_time,end.lw,(end.lw-beg.lw))   lw,
      
decode(end.startup_time,end.begin_interval_time,end.tbr,(end.tbr-beg.tbr)) tbr,
      
decode(end.startup_time,end.begin_interval_time,end.tbw,(end.tbw-beg.tbw)) tbw
FROM

(SELECT
dba_hist_snapshot.snap_id, startup_time, begin_interval_time, end_interval_time,
sum(decode(
stat_name,'physical read total IO requests',value,0)-
decode(
stat_name,'physical read total multi block requests',value,0)) sr,
sum(decode(
stat_name,'physical write total IO requests',value,0)-
decode(
stat_name,'physical write total multi block requests',value,0)) sw,
sum(decode(
stat_name,'physical read total multi block requests',value,0)) lr,
sum(decode(
stat_name,'physical write total multi block requests',value,0)) lw,
sum(decode(
stat_name,'physical read total bytes',value,0)) tbr,
sum(decode(
stat_name,'physical write total bytes',value,0)) tbw
  
FROM wrh$_sysstat, wrh$_stat_name, dba_hist_snapshot
WHERE
wrh$_sysstat.stat_id = wrh$_stat_name.stat_id
  
AND wrh$_sysstat.snap_id = dba_hist_snapshot.snap_id
group
by dba_hist_snapshot.snap_id, startup_time, begin_interval_time, end_interval_time) beg,
(SELECT
dba_hist_snapshot.snap_id, startup_time, begin_interval_time, end_interval_time,
sum(decode(
stat_name,'physical read total IO requests',value,0)-
decode(
stat_name,'physical read total multi block requests',value,0)) sr,
sum(decode(
stat_name,'physical write total IO requests',value,0)-
decode(
stat_name,'physical write total multi block requests',value,0)) sw,
sum(decode(
stat_name,'physical read total multi block requests',value,0)) lr,
sum(decode(
stat_name,'physical write total multi block requests',value,0)) lw,
sum(decode(
stat_name,'physical read total bytes',value,0)) tbr,
sum(decode(
stat_name,'physical write total bytes',value,0)) tbw
  
FROM wrh$_sysstat, wrh$_stat_name, dba_hist_snapshot
WHERE
wrh$_sysstat.stat_id = wrh$_stat_name.stat_id
  
AND wrh$_sysstat.snap_id = dba_hist_snapshot.snap_id
group
by dba_hist_snapshot.snap_id, startup_time, begin_interval_time, end_interval_time) end
WHERE
beg.snap_id + 1 = end.snap_id
)

order
by 1;

 

--- REWRITTEN TO COVERT DECODE FUNCTION TO CASE STATEMENT

 

SELECT end_time,
        
ROUND (sr / inttime, 3) sri,
        
ROUND (sw / inttime, 3) swi,
        
ROUND ( (sr + sw) / inttime, 3) tsi,
        
ROUND ( sr
              
/ CASE
                  
WHEN sr + sw = 0 THEN 1
                  
ELSE (sr + sw)
                
END
               *
100,
              
3)
           srp
,
        
ROUND ( sw
              
/ CASE
                  
WHEN sr + sw = 0 THEN 1
                  
ELSE (sr + sw)
                
END
               *
100,
              
3)
           swp
,
        
ROUND (lr / inttime, 3) lri,
        
ROUND (lw / inttime, 3) lwi,
        
ROUND ( (lr + lw) / inttime, 3) tli,
        
ROUND ( lr
              
/ CASE
                  
WHEN lr + lw = 0 THEN 1
                  
ELSE (lr + lw)
                
END
               *
100,
              
3)
           lrp
,
        
ROUND ( lw
              
/ CASE
                  
WHEN lr + lw = 0 THEN 1
                  
ELSE (lr + lw)
                
END
               *
100,
              
3)
           lwp
,
        
ROUND ( (tbr / inttime) / 1048576, 3) tr,
        
ROUND ( (tbw / inttime) / 1048576, 3) tw,
        
ROUND ( ( (tbr + tbw) / inttime) / 1048576, 3) tm
  
FROM (SELECT beg.snap_id beg_id,
                
end.snap_id end_id,
                 beg
.begin_interval_time,
                 beg
.end_interval_time,
                
end.begin_interval_time begin_time,
                
end.end_interval_time end_time,
                  
( EXTRACT (
                      
DAY FROM ( end.end_interval_time
                                
- end.begin_interval_time))
                   *
86400)
                
+ ( EXTRACT (
                      
HOUR FROM ( end.end_interval_time
                                  
- end.begin_interval_time))
                   *
3600)
                
+ ( EXTRACT (
                      
MINUTE FROM ( end.end_interval_time

                                     - end.begin_interval_time))
                   *
60)
                
+ ( EXTRACT (
                      
SECOND FROM ( end.end_interval_time
                                    
- end.begin_interval_time))
                   *
01)
                   inttime
,
                
CASE
                  
WHEN end.startup_time = end.begin_interval_time THEN end.sr
                  
ELSE (end.sr - beg.sr)
                
END
                   sr
,
                
CASE
                  
WHEN end.startup_time = end.begin_interval_time THEN end.sw
                  
ELSE (end.sw - beg.sw)
                
END
                   sw
,
                
CASE
                  
WHEN end.startup_time = end.begin_interval_time THEN end.lr
                  
ELSE (end.lr - beg.lr)
                
END
                   lr
,
                
CASE
                  
WHEN end.startup_time = end.begin_interval_time THEN end.lw
                  
ELSE (end.lw - beg.lw)
                
END
                   lw
,
                
CASE
                  
WHEN end.startup_time = end.begin_interval_time THEN end.tbr
                  
ELSE (end.tbr - beg.tbr)
                
END
                   tbr
,
                
CASE
                  
WHEN end.startup_time = end.begin_interval_time THEN end.tbw
                  
ELSE (end.tbw - beg.tbw)
                
END
                   tbw
          
FROM ( SELECT dba_hist_snapshot.snap_id,
                           startup_time
,
                           begin_interval_time
,
                           end_interval_time
,
                         
SUM (
                              
CASE
                                
WHEN stat_name =
                                      
'physical read total IO requests' THEN
                                  
VALUE
                                
ELSE
                                  
0
                              
END
                            
- CASE
                                
WHEN stat_name =
                                      
'physical read total multi block requests' THEN
                                  
VALUE
                                
ELSE
                                  
0
                              
END)
                             sr
,
                          
SUM (
                              
CASE
                                
WHEN stat_name =
                                      
'physical write total IO requests' THEN
                                  
VALUE
                                
ELSE
                                 
0
                              
END
                            
- CASE
                                
WHEN stat_name =
                                      
'physical write total multi block requests' THEN
                                 
VALUE
                                
ELSE
                                  
0
                              
END)
                             sw
,
                          
SUM (
                            
CASE
                              
WHEN stat_name =
                                    
'physical read total multi block requests' THEN
                                
VALUE
                              
ELSE
                                
0
                            
END)
                             lr
,
                          
SUM (
                            
CASE
                              
WHEN stat_name =
                                    
'physical write total multi block requests' THEN
                                
VALUE
                              
ELSE
                                
0
                            
END)
                             lw
,
                          
SUM (
                            
CASE
                              
WHEN stat_name = 'physical read total bytes' THEN
                                
VALUE
                              
ELSE
                                
0
                            
END)
                             tbr
,
                          
SUM (
                            
CASE
                              
WHEN stat_name = 'physical write total bytes' THEN
                                
VALUE
                              
ELSE
                                
0
                            
END)
                             tbw
                    
FROM wrh$_sysstat, wrh$_stat_name, dba_hist_snapshot
                    
WHERE wrh$_sysstat.stat_id = wrh$_stat_name.stat_id
                     
AND wrh$_sysstat.snap_id = dba_hist_snapshot.snap_id
                
GROUP BY dba_hist_snapshot.snap_id,
                           startup_time
,
                           begin_interval_time
,
                           end_interval_time
) beg,
                
( SELECT dba_hist_snapshot.snap_id,
                           startup_time
,
                           begin_interval_time
,
                           end_interval_time
,
                          
SUM (
                              
CASE
                                
WHEN stat_name =
                                      
'physical read total IO requests' THEN
                                  
VALUE
                                
ELSE
                                 
0
                              
END
                            
- CASE
                                
WHEN stat_name =
                                      
'physical read total multi block requests' THEN
                                  
VALUE
                                
ELSE
                                  
0
                              
END)
                             sr
,
                          
SUM (
                              
CASE
                              
WHEN stat_name =
                                      
'physical write total IO requests' THEN
                                  
VALUE
                                
ELSE
                                  
0
                              
END
                            
- CASE
                                
WHEN stat_name =
                                      
'physical write total multi block requests' THEN
                                  
VALUE
                               
ELSE
                                  
0
                              
END)
                             sw
,
                          
SUM (
                            
CASE
                              
WHEN stat_name =
                                  
'physical read total multi block requests' THEN
                                
VALUE
                              
ELSE
                                
0
                            
END)
                             lr
,
                          
SUM (
                            
CASE
                              
WHEN stat_name =
                                    
'physical write total multi block requests' THEN
                                
VALUE
                              
ELSE
                                
0
                            
END)
                             lw
,
                          
SUM (
                            
CASE
                             
WHEN stat_name = 'physical read total bytes' THEN
                                
VALUE
                              
ELSE
                                
0
                            
END)
                             tbr
,
                         
SUM (
                            
CASE
                              
WHEN stat_name = 'physical write total bytes' THEN
                                
VALUE
                              
ELSE
                                
0
                           
END)
                             tbw
                    
FROM wrh$_sysstat, wrh$_stat_name, dba_hist_snapshot
                    
WHERE wrh$_sysstat.stat_id = wrh$_stat_name.stat_id
                      
AND wrh$_sysstat.snap_id = dba_hist_snapshot.snap_id
                
GROUP BY dba_hist_snapshot.snap_id,
                           startup_time
,
                           begin_interval_time
,
                           end_interval_time
) end
          
WHERE beg.snap_id + 1 = end.snap_id)
ORDER
BY 1;