Tags

, ,

During my earlier work as a consultant and in my todays job as a technical supporter to the development team I encountered various SQL & PL/SQL antipatterns. I would like to share some of them with you.

Another sign of programmers laziness is the wide usage of “select *”. This antipattern can be found in inline views, database views, cursors …

The only column needed in the following example is the employee_id, so why do we read the whole row?

This is a waste for many reasons:

  • expensive columns like CLOB/BLOB/XMLTYPE
  • chained rows in the table
  • maybe we could benefit of an index-only access if we pared the query down to the minimum
  • storage that is needed to hold the row

Which Columns are really needed?

For this PL/SQL block we would have to return employee_id only from the implicit cursor.

begin
   for EmpRec in (select *
                    from employees
                   where salary < l_minSalary) loop
      raise_salary(in_employee_id => EmpRec.employee_id);
   end loop;
end;

In the following example the only relevant columns needed as result of the inline view on GR_BRCT_STWE (which has 39 columns) are GR_LNR_1 to join GR_STAMM and GR_LNR_2 to filter against a bind variable (which could be done in the inline view anyway) . 

select count(*)
  from (select *
          from GR_BRCT_STWE
         where USERBK_NR = :2) verb
  join GR_STAMM mfh on (    verb.GR_LNR_1 = mfh.GR_LNR
                        and mfh.GR_TYP_CD = 3 
                        and mfh.USERBK_NR = :2)
  join GR_GBDE mfh_gbde on (    mfh_gbde.GR_LNR = mfh.GR_LNR
                            and mfh_gbde.USERBK_NR = :2)
 where verb.GR_LNR_2 = :1
  and mfh_gbde.WHG_ANZ is not null;

If we have to use most but not all of the columns of a table it might be cumbersome to write down all the columns, but nowadays we have great support by IDEs like SQL Developer.

SQL Developer may help you…

If you write down a select * from table you will discover a yellow wavy line underneath the asterisk. Hovering your mouse pointer over this line will enable you to apply the complete column list, which may be arranged afterwards.

Efficient coding? Sorry, I call it laziness.

Yes it may be more work to specify exactly which columns you need, but not doing so is not a sign of efficiency but only of laziness.

Conclusion

Knowing bad habits is the best way to avoid them.

Most of the examples I found are based on:

  • copy/paste coding
  • sticking to things that worked in the past (without questioning them)
  • ignoring new features

This (and the other posts on the anti-patterns) should be a help to get over them.