Lots of my tips have to do with very specific features of PL/SQL. Like deterministic functions, for example. They can be useful for code maintenance, testing, and separating your deterministic code from your non-deterministic code (which is most of what you write, including all SQL statements).
I talked about deterministic functions in Feuertips #42 “When functions are deterministic”. That tip session sparked a whole lot of thinking by one of our viewers, Monika Lewandowska, and several very interesting tweets. So I invited Monika to present her ideas in Feuertip #45, Here below.
Monika also was kind enough to share her slides of the presentation here. Even better, she loaded up her demo scripts on LiveSQL.
In a nutshell, Monika’s argument is an extension of mine:
Don’t just look inside individual procedures and functions to identify deterministic code. Look at your entire application or process architecture. By separating out the data retrieval and update from the algorithmic code, you might be able to greatly improve performance and much more.
This approach isn’t always appropriate (it’s more likely to help you with batch processing than short-burst transaction code), but it’s definitely worth a look.
I enjoyed this conversation in many different ways. One of them is that her approach was at least partially inspired by a training I gave in Warsaw in 2007. Ah, those were the days. The days when my presentations contained slides this like:
And for a while I was also having a blast promoting this strange idea (for an Oracle Database developer):
My basic argument went like this:
- Hard-coding is bad, right?
- The most dangerous hard-codings are your SQL statements. (they can only represent your data model as it exists, today)
- Therefore…..SQL is bad.
Pretty neat, huh? The “power” of logic!
