Database application developers are taught very early that while using SELECT * is fine for ad-hoc exploration of a database, one should not use it in a real application, instead including each required column in the SELECT clause. There are two main reasons for this: First, because you're going to be pulling the query result into some kind of local structure or set of variables, such code will be rendered inoperable by a change to the table structure. Second, a particular piece of code ought to be only pulling the data it needs, and not just blindly retrieving everything available. It's wasteful to pull more than you need.
However, it's considered good practice to separate data access from business logic; I recall you writing on the topic of limiting the amount of SQL in your PL/SQL and isolating data access. When doing that, you may end up seeing a need to pull multiple distinct subsets of columns from the same table for various purposes (procedure A needs columns 1, 2, and 3, procedure B needs columns 1, 3, and 7, etc.). In order to simplify things, you might look at consolidating these functions into one that can accept a range of criteria and will simply return either an entire row or a collection of such rows. This would be a case of trading efficiency for maintainability, and while it's in defiance of the second argument given above, maintainability can trump efficiency if both alternatives have acceptable performance.
Given that one might want to retrieve entire rows, and that PL/SQL provides the ability to use %ROWTYPE in declarations of records and collections, might we actually find the use of SELECT * to be viable? It circumvents the first argument by continuing to work correctly in the face of changes to table structure (the table alteration knocks a package invalid, you recompile, and you're back in business). It actually could be helpful because now you often won't need to change your data-access procedure when a table changes; you need only change code in its callers to fix references to affectced columns. You would of course need to make changes to the data-access code if a new or deleted column can appear in a WHERE clause in the procedure.
I have used this technique successfully on a limited basis, though every instinct I had cried out in agony as I did it. I've had some spirited discussions about it with colleagues of the DBA persuasion. I just wondered if you had some thoughts on the topic.
Answer:
Well, as some of you might be aware, I have thoughts on almost any topic—but that doesn't mean they are worth listening to! In this case, while I don't feel agonized about your dilemma, I do share some of your feelings on the matter.
Theoretically (and on one hand), SELECT * can be wasteful in that you retrieve more data than you really need in your program. Practically (and on the other hand), for just about every application we PL/SQL developers write, the price paid in memory and CPU for selecting all columns into a %ROWTYPE record will be negligible and unnoticed. So if by taking this approach we end up with code that is more resilient to change and easier to maintain, then I say go for it!
To sum up in a more general way: very few of us write programs simply because we want to build perfect logical structures. We solve problems and meet requirements with our code. I definitely advocate doing things the "right way" and avoiding "quick and dirty" solutions (this preference is, after all, the fundamental impulse driving the development of my new tool, Qnxo). The "right way" should not be established, however, in the abstract; it should be defined within the context of your project.
No comments:
Post a Comment