The long and winding road I
December 15, 2006 Leave a comment
A friend and former colleague of mine is starting out on the journey from Java Developer to Oracle Developer/DBA.
I believe that he is probably tired of the incessant changing Java landscape, the political nonsense, every new feature being the latest and greatest saviour only to usurped by the next one, the bloatedness and the over-engineering.
Not that Java isn’t a good thing. Of course, it is. But from a developer perspective, I can imagine that it can get a bit tiring. Not that the Oracle Developer/DBA world isn’t challenging. Behemoths such as Tom Kyte, Jonathan Lewis et al might bestride the Developer and DBA worlds like collosi, but for most of us mere mortals just keeping on top of what we think we might know whilst keeping tabs on new stuff coming along, is a mighty challenge.
Anyway, I digress.
It seems that my friend is enjoying looking at Oracle through the eyes of an experienced OO practitioner. And he’s interested in the data logic layer in Oracle using stored procedures and encapsulating access in an appropriate way.
More precisely, on this occasion, he was interested in wrapping some cetain conditioned selects in multiple functions returning ref cursors and then in an outer procedure calling these functions something like this:
select f_get_xxxx() union f_get_xxxx() union f_get_xxxx() union f_get_xxxx()
From an encapsulation and code reuse perspective, this would all be nice and neat.
From a database efficiency and performance perspective, this would probably not be a good thing.
Of course, whatever ideas you’re toying with you need to test and benchmark and compare. Tom Kyte’s runstats package would be an excellent choice here. If I have time later, I might add in some illustrations that might show whether this was in fact a good or bad thing. And obviously how good or bad it was would depend on the specifics of what you were doing.
However, in theory and as a general rule of thumb and at the most basic level, let’s say there are four functions that each select from TABLE A using a full table scan and a different condition.
By using the encapsulated function approach, you are likely to be doing four full table scans and then some sort of sort to satisfy the unions.
Whereas if you wrote it once, using ORs to satisfy the different conditions, you’d likely be looking at a single full table scan.
I think one of the main challenges of Oracle development, particularly if you’re coming from an OO background, is thinking in SETS. It’s so crucial for efficient SQL and PLSQL. The general approach should be to try and do it one bit of SQL using set operations if appropriate, if not then use bulk operations, and only failing that then the iterative PLSQL approach.
And to be fair, the encapsulation approach is thinking in sets, but not at a higher enough level. Do it in as little SQL as you can.
But, of course, there should always be a balance between efficiency, speed and maintainability. And that’s where the testing comes back in. For you might find that in your specific circumstance, although there may be a slight performance trade off, the gains in reuse, maintainability and extendability are worth it.
However, chances are that if you end up doing four times the IO that you need to do, then this is going have some unpleasant side effects somewhere along the line.