[From a former PL/SQL product manager]
This mantra is often recited by famous speakers at conferences about Oracle Database:
“If you can do it in SQL, do so; only if you can’t, do it in PL/SQL”
But should it be slavishly obeyed in all circumstances? As with all best practice prescriptions, the answer is “It depends”.
This paper examines alternative solutions to a well-known problem:
How to compute non-overlapping RowID ranges that completely cover a nominated table and that all contain, as nearly as is possible, the same number of rows.
It’s relatively straightforward to implement a solution that simply orders the rows in the table by RowID and then allocates them into chunks that all have either S or S+1 rows; but for candidate tables of typical size, this exact method takes unacceptably long to execute. Therefore, an approximate method is called for – approximate, that is, by being uneven in the number of rows that each chunk holds. You can find examples of such approximate solutions using Internet search. They all use the information maintained by Oracle Database and exposed in DBA_Extents rather than the table itself. However, all solutions that I have seen impose special limitations in pursuit of brevity of exposition – and they leave generalization as an exercise for the reader. It turns out that there’s more to the generalization than meets the eye.
I use the vehicle of the various implementations of each kind of method to discuss issues of best practice – both SQL versus PL/SQL, and within PL/SQL. Each competing choice is implemented as a Create_Chunks() procedure in a dedicated PL/SQL package. The choice between SQL and PL/SQL is therefore made in the context of the implementation of the Create_Chunks() procedure. It would be entirely foolish to implement the procedural code that is non-negotiably needed to issue SQL statements outside of the database.
All solutions examined pass the same correctness tests; but there are performance differences. More significantly, there a big differences in source code size and in general clarity. My conclusion is that, for this particular example, it is better to implement the core logic of the algorithms in PL/SQL, even when a single insert... select approach is feasible. My preference is inevitably subjective – and reflects some rather subtle considerations. Your opinion might be different.
My presentation slides are no use without me presenting them. I have therefore written a whitepaper, HERE, that gives a careful and detailed account of what my presentation addresses.
I also provided a zip file, HERE, of working versions of all the implementations that the paper discusses together with the test harness.
Note added 22-May-2016
I presented this topic at the HOTSOS Symposium in March 2016. OTN's Laura Ramsey interviewed me afterwards and posted the video HERE.
We talked about the backstory of the Oracle Database Community's effort that generated many interesting SQL solutions. The ones that my paper discusses came from Jonathan Lewis (@JLOracle) and Stew Ashton (@StewAshton). Stew credits Sven Weller (@SvenWOracle) for a major insight that kick-started his solutions.