PL/SQL and edition-based redefinition:
the perfect marriage.

  • October 28, 2015

Transforming one table to another: SQL or PL/SQL?

Steven Feuerstein
Developer Advocate for PL/SQL

[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.

Jonathan seeded the crowd-sourcing work with this OTN forum posting. And Stew wrote up the account of his work in this blog post.

It was a fun project. I've presented the work at quite a number of Oracle User Group conferences. And I had the privilege and pleasure of bringing the contributors to the stage to explain their work at several of the gigs: at OpenWorld 2015 and at UKOUG 2015, I was joined by Jonathan and Stew; at DOAG 2015 I was joined by Jonathan and Sven; and at BGOUG Fall 2015 I was joined by Jonathan.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.