Intro to Recursive Structures for Database Developers

Presented by: Michael Rosenblum, Dulcian Inc

As long as new developers come to the IT world, they will be challenged by recursions – but in the database world, you are in double-trouble: in addition to recursive code you have recursive data. This presentation demystifies both of those areas!

The first half of the presentation is dedicated to recursive PL/SQL with the focus on exception handling (one of the least understood areas in the whole database development) and variable management (another area where developers are not very clear about what is going on at every level). The second half is covering SQL techniques – Oracle-specific CONNECT BY clause and SQL standard Common Table Expressions (CTE). In addition to syntax intro the audience will see how CBO internally transforms recursive queries (that will help to understand query results) and how things like joins or complex conditions impact execution plans /corresponding efficiency (that will make developers be performance-conscious from the very beginning). Recursions are not magical – they just have to be explained well enough to understand what is going on!