Monday, June 9, 2008

Do more with SQL

Today I will give an in-house knowledge session called "Do more with SQL". I want to share my knowledge about this topic, because many times I see procedural code being written where one or a few single SQL statements would have been sufficient. And not only sufficient, but much shorter, more readable and faster as well. I hope to raise awareness among my colleagues for this problem. And if this mindset of doing more in SQL is embraced, it would be a pity if you think that a certain problem cannot be solved with SQL alone. This might have been true in version 7, but certainly not anymore nowadays with Oracle version 11.

So the session will start with an introductory story about why you would want to do more with SQL instead of using procedural code, followed by showing several alternatives for all common SQL techniques. Row generation (thanks again Laurent!), interval based row generation, splitting comma separated strings, string aggregation and pivot/unpivot. For most of the readers of this blog, these techniques will likely be well known. They are the frequently answered questions on Oracle Forums, where a typical answer is "use the search facility and search for [technique name here]", because they have been answered numerous times. However, the same questions keep popping up everyday, on the Oracle Forums as well as on the work floor. So the story cannot be told often enough apparently.

Due to several inspiring blog posts from Karen Morton, especially this one, I decided to severely restrict the number of bullet point slides. I hope the new style will work out.

The presentation and the accompanying scripts can be downloaded here. Usual warning: they are in Dutch. And an extra warning: the size of the powerpoint presentation is almost 4M ...

Powerpoint presentatie Doe meer met SQL

Demonstratiescripts Doe meer met SQL