re: binds
cosmin, November 21, 2005 - 8:10 pm UTC
<<thou shalt have a really good reason for NOT using a bind variable, binds>>
how could I have! -- three books later, harping it at most every conference and in so many posts, I still missed it!!! ;-) and yeah, bind variables has been an almost foreign concept here, together with (proper) indexes...
thanks a bunch,
Cosmin
0.5
A reader, November 22, 2005 - 3:07 am UTC
"
thou shalt actually design your application before writing code. You must
design to scale and design to be secure, they cannot be afterthoughts.
"
So true.
should be No. 0
Just inherited another application that has to be rewritten
cause of this
Q: what does peers mean in this context
Henk Boerboom, November 22, 2005 - 4:57 am UTC
For a dutch reader:
What does 'peers' mean in
10. Thou shalt review code with peers.
and, what does command no. 4 mean ?
4. Thou shalt instrument thy code.
btw. I don't think that Moses wrote with with w/ !?!
my 2cts
3. Thou shalt think in sets. (As in set theory from Georg Cantor)
November 22, 2005 - 8:38 am UTC
</code>
http://dictionary.reference.com/search?q=peer <code>
code instrumentation is the fine art of making every other line of code be debug. v$ tables - they are instrumention in the oracle server. sql_trace=true, code instrumentation. the event subsystem - instrumentation. trace files - instrumentation. alert log, instrumentation.
more
Marc Blum, November 22, 2005 - 6:21 am UTC
- Gather your requierements thoroughly.
- Ask, ask, ask.
- Design your interfaces first.
- Design and implement your tests before coding.
- Review all and everything: requierements, design, interfaces, test, code.
- Start coding as late as possible.
November 22, 2005 - 8:40 am UTC
by interfaces you mean data interfaces (data exchange) not UI :) right?
many developers today hear interface and think "GUI"
"what does peers mean"
cosmin, November 22, 2005 - 7:55 am UTC
Colleagues, friends, co-workers
<<4. Thou shalt instrument thy code.>>
putting debug statements or using dbms_profile to measure the duration of each code, and not only...to have an audit trail in case of complex logic so you know how each procedure is called.
<<btw. I don't think that Moses wrote with with w/ !?!>>
but then again, he did not have a computer and blogs were not that advanced back then either. I'm sure in a few thousand years we'll be taking telepathy, mind (and body) travel and other cute things...
<<3. Thou shalt think in sets. (As in set theory from Georg Cantor)>>
ahh, but that's a good one...
maybe I should have called the thread "the 10 commandments (plus 10 more to spare)" :-)
thx much,
Cosmin
Replacement
Bob B, November 22, 2005 - 8:42 am UTC
I vote for replacing
"7. Thou shalt stress test w/ significant data."
with
"7. Thou shalt test thy code to destruction."
November 22, 2005 - 8:51 am UTC
courtesy of Jonathan Lewis of course (Pratical Oracle 8i, building efficient databases)
This just happened.
Andrew, November 22, 2005 - 9:37 am UTC
How about 'know how thy databse works. I.e.: concurrency management'
A brief, and very true -- I could not have made this one up if I tried -- story.
I went to a cellular store (company owned and operated) to buy my son a cell phone for his birthday, three days ago. In the process of setting up the phone and account they allow you to choose from a list of telephone numbers. I chose the phone number and the store person completed the transaction.
The next day, when we tried out the phone, he could call out fine, his telephone number showed up on caller-id -- as it should. But, when we tried to call back on that number it went to some one else's cell phone.
Went back to the store and -- all in all it took two days to get it fixed (new phone number) and the billing info switched over to the new number.
The store person explained that this problem can occur if two people choose the same phone number at the same time and hit 'enter' at the same time.
Can you say 'concurrency manaagement'
This is a great example of how two transactions can collide in the system if concurrency is not mamaged properly.
Jeff Hunter, November 22, 2005 - 10:08 am UTC
Thou shalt kneel in the presence of thy database administrator.
November 22, 2005 - 10:28 am UTC
big thumbs down from me on that one!
Thou shalt maximize sql and minimize pl/sql."
P.Karthick, November 22, 2005 - 10:22 am UTC
"Thou shalt maximize sql and minimize pl/sql."
I was told SQL engine is more powerfull than PL/SQL engine and hense use more of SQL than PL/SQL and thats why Oracle is also introducing lot of new SQL Statements that put to gether a set of PL/SQL statements e.g. Merge Statement
November 22, 2005 - 10:29 am UTC
the less code you and I write, the better things are...
to Henk (Georg Cantor)
A reader, November 22, 2005 - 10:57 am UTC
Georg Cantor mainly was interested in infinite sets.
in sql every set is finite (nearly every), so
it suffices to think in sets the way pupils learnt to
in primary school about 30 years ago
"Thou shalt maximize sql and minimize pl/sql."
Jon, November 22, 2005 - 12:35 pm UTC
I disagree with this one. It suggests that writing the same code over and over again in different places is better than having a single stored procedure returning a ref cursor. The duplication of business logic causes problems, it increases hard parsing, and it doesn't promote the use of bind variables. Why? Because a long time ago, Oracle performed better with SQL than PL/SQL.
For application development, my rule would be "write stored procedures and return ref cursors instead of embedding SQL in multiple places". This promotes reusing code and makes the use of bind variables so much easier. Plus, you increase the likelihood of soft parsing because "everyone" uses the same query.
And then there is the support issue. What DBA wants to dig through the new language of choice to find a SQL statement for revision? It is so much easier to just edit the stored procedure and have the SQL enhanced.
November 22, 2005 - 4:22 pm UTC
no it doesn't imply that at all.
It means don't code:
for x in (select * from t)
loop
insert into another_t values X;
end loop;
it means do code:
insert into another_t select * from t;
yes, building stored procedures to make certain sql reusable - good idea. But that doesn't mean "write a pipelined function to return results" for example.
And another...
Scott, November 22, 2005 - 12:38 pm UTC
Thou shalt ask 'Why', early and often.
--courtesy of Tom's keynote at the NorCal User Group meeting.
Jeff Hunter, November 22, 2005 - 12:56 pm UTC
"big thumbs down from me on that one!"
OK, OK. How about "Thy shall not ignore the database administrator because he doen't write Java code"?
November 22, 2005 - 4:23 pm UTC
two thumbs up :)
to Jon
cosmin, November 22, 2005 - 1:22 pm UTC
Jon, I think you missed a point.
when I said maximize sql while minimizing pl/sql I meant maximixe the sql coding within packages and minimize the "slow by slow" (row by row) pl/sql processing. Oracle has gone through considerable pain and been quite successful to bring the performance of pl/sql up to par with that of sql, through the use of bulk processing. No other feature in pl/sql, in my oppinion (generally speaking) comes close to the spead of sql/joins.
<<Because a long time ago, Oracle performed better with SQL than PL/SQL.>>
come again!? a long time ago, Oracle performed better with SQL than PL/SQL. I think you've read too much Steven Feuerestein stuff. Those books by him are powerful, PL/SQL is awesome in its own right, there are many uses for PL/SQL which make it appropriate but, by and large, SQL is so much faster than PL/SQL.
<<For application development, my rule would be "write stored procedures and return ref cursors instead of embedding SQL in multiple places". This promotes
reusing code and makes the use of bind variables so much easier. Plus, you
increase the likelihood of soft parsing because "everyone" uses the same query. >>
now you're talking!! yes!
<<And then there is the support issue. What DBA wants to dig through the new
language of choice to find a SQL statement for revision? It is so much easier
to just edit the stored procedure and have the SQL enhanced. >>
nobody said anything of writing any sql code outside of packages. Packages are awesome, write code as close as possible to the database, ie, through packages. Any app that has a client heavy logic should be looked at to move all that processing on the server. Yes, there are special circumstances and necessities for having some code on the client, for validation in some cases, but not much more..... everything should be done on the backend, as sets & bulk processing.
"Slow-by-slow" is long dead in this age of microseconds optimizations and processing exponentially larger data in the same 24 hours period.
interesting one
Denis, December 02, 2005 - 1:00 pm UTC
Interesting list, and one I mostly agree with. However I disagree in general with the first commandment.
In my opinion prior to writing a comment a developer should ask 'why do I _need_ to comment this code?'. 9 times out of 10 the answer is 'because the code is more confusing than it needs to be'. Next thing to do is _not_ write a comment, but refactor your code so that it is obvious (often by extracting the hard to understand code into a procedure with a name that can substitute for the comment). We find in our team that we actually comment very rarely. In fairness I must point out we use XP so all developers are familiar with all code, so maybe it would not go so smoothly if developers were more compartmentalised.
Indeed one other commandment I would suggest would be 'Write the test first!'. We use a java tool called dbunit to populate appropriate tables with data for every procedure, and we write java based tests to run the procedure in a variety of ways. The tests run automatically every hour, and it gives us real peace of mind that new development hasn't broken the system, even when we make changes to a critical part of the code.
These tests also act as a kind of living comment, because they assert their own veracity frequently as opposed to a typical old comment that often actually can confuse a developer if they are not updated. A test _has_ to be updated to reflect reality or else the build breaks and people get cross.
December 03, 2005 - 9:55 am UTC
we'll have to violently disagree on this one. Even if your code fits on the screen (which most code, sigh, won't), code is code - code is not "text", code is not narrative, code is literal (can have bugs), comments say "this is what this is supposed to do, this is how we are going to do it".
and 5 years from now when all of your eXtreme Programmers have moved on - what then? What about the poor soles that follow in your footsteps? The ones that are not familar with the code from day one?
Fair enough
Denis, December 03, 2005 - 11:00 am UTC
Yup, we'll have to violently disagree Tom. In five years time, if the code we've written is still in use (which it hopefully will) the 'poor souls' who follow us will have a suite of regression tests humming away in the background asserting that the business logic our code is meant to uphold is, in fact, being upheld. I also think it's not realistic that in 5 years time an entirely new set of developers will magically drop into the company and have to piece together the web we've weaved through the comments in the code - that's not the way it works. The most effective means of communicating the meaning of any code has been and always will be conversation - I would say an order of magnitude more useful than any code comment. These conversations occur all the time and help increase everyone's knowledge of the code.
Now I must say we've recently added a new member to our team in the last few weeks, (he's come from a non-XP envionment), and I've been watching with interest how he's finding the way we work. He reckons he'd understand the code a little faster if there were comments (naturally), however it is _not_ a major issue for him, and the benefits of pairing with members of the team seem to be allowing him to adjust rapidly (his adjustment includes having to learn java as well by the way). I believe procedure names should almost always be adequate to answer the 'this is what we're going to do' question, and not many things are complex enough to merit a 'this is how we're going to do it'. Pairing also seems to have introduced what I might call a common idiom to our code base, in so far as similar business requirements seem to be done in a similar way across different parts of the system, meaning that a newcomer only has to come to terms with a handful of patterns to be fairly knowledgeable about the whole system. Whatever about commenting (it's one of the XP practices that doesn't get me too excited actually - if you want to comment, go ahead, if you don't, don't) I can't stress enough the liberation that test driven development, pairing and automated regression testing give. You get a huge suite of tests (we have thousands for a 5 member team), running all the time, with a team all of whose numbers know at least a little bit about every part of the code. None of the practices I mention will subvert the fundamental truism of software development that good developers will write good programs, bad ones bad programs, but I'd urge teams that don't have automated regression testing systems (with, naturally, tests) in place to give it a go - it's - shall I say - relaxing.
December 03, 2005 - 11:39 am UTC
no documentation? no comments? just code? how much code are we talking about here? How does anyone know how it all fits together?
patterns smatterns, I'm talking logic and flow. So I see a pattern, what does that tell me about what this whole entity does, or why, or what.
I like the other aspects (test and regression testing - but that is a given), but lack of documentation? Relying on people reading/knowing the entire codebase and sort of running it in their heads - or having that knowledge passed down by word of mouth from generation to generation?
Another reason to comment
Glenn Feiner, December 03, 2005 - 11:57 am UTC
My managers require documenting what my software does. By using comments and auto-generating tools, It becomes a little less of a bother. For PL/SQL - I found PL/SQL Developer to be pretty nice - cheap too!
to Denis; re: no documentation
cosmin, December 03, 2005 - 3:31 pm UTC
unfortunately Denis, if you've been long enough in the industry, make that ten years or more, working in many companies, analyzing hundreds of thousands of lines of code, debugging, analyzing, re-writing, I'm sure you'll come to agree that "documentation" is critical and indispensable. To me, I have seen many "awesome" developers, time and time again, who have "15-20 years" of development under their belt, who could not tell me what their code does or what it is supposed to do. What they intend to write with what they're actually writing, let's just say it's like apples and oranges.
Many times, when I review code with them, I often ask them first what the particular code is supposed to do (for lack of documentation) and then point out to them the many errors or logic, coding, etc etc. What they intend to do with what they write, well, let's just say it's apples and oranges. No documentation -- leaves me to a wide range of speculation of why/when/how/where.
On top of that, other developers, who may not be as proeficient, might take that as "gospel code" and be intimidated and not change it because it's been there, in production, for eons.
Documentatation helps everyone, the writer, the reader, the debugger, the audit company, etc.
10,000,000 lines of Spaghetti code and no one to maintain it.
Andrew, December 03, 2005 - 3:45 pm UTC
Commented code is not only for the new crew of support programmers that may come along in five years. What about the current support crew member who revisits a source two years from now -- or even six months from now. Do you expect him to remember what a particular module is supposed to do? Or where to find that one little part that requires the (in)famous 'one line chage' is. I have seen both the useless comment in a program like 'This is a simple two file match. If you don't understad it, you shouldn't be looking at it' (yes that is a real comment in from real program that I had to support), and the very helpful which explain things like 'Here we total the order value by first resetting the accumulator, then adding to it for each order line. When we see a new order number we write out the order number and total, and reset for the next order.' With a comment like that, any programmer can instantly know what we are trying to do in the code that follows and will be much more efficient (read cost effective/productive) and accurate in his analysis and modifications.
Fewer comments may be fine in simple and straight-forward code, but sometimes we cannot afford the 'luxury' of breaking down complex or compound statements into simple singleton statements because application performance will suffer way to much. I would much rather have a well documented complex PL/I statement full of offsets and substrings, for example, that does in one step what would otherwise take 10 or 12 because when I pass a million records through it the former will complete in a couple of minutes where the latte will run for hours. Similarly, I would much rather have a well documented highly complex sql statement that does lots of work by going to disk only once, rather than a simple to read and self documenting cursor loop that takes all day and half the night to run.
So I agree with the first commandment -- comment allyour code -- and maintian the comments.
Good to see interest in this
Denis, December 04, 2005 - 10:00 am UTC
Hi guys,
It's always good to hear cogent arguments like these. I suppose I should really emphasise again that my opposition to 'comment your code' commandment does _not_ mean that I believe the opposite 'never comment your code' commandment. As I said before, I think commenting code should be a last resort after attempts to make the code self explanatory are exhausted.
I totally agree with Andrew that complicated sql being used for performance reasons over some more easy to read but less performant step by step code should be documented - but _only_ if the sql is too complicated for a developer with reasonable sql knowledge to grasp the gist of it by reading the sql itself. To be honest I would expect one of our current developers to remember what a particular module does if he/she revisits it after 6 months. If it is clearly named and only does one thing I don't see why this should be difficult. (In fact it happens frequently in our team and no-one has raised this as an issue). Your sample of a helpful comment is interesting - I would have thought that the code for this example would be straightforward to understand if written clearly and wouldn't merit a comment at all.
Cosmin, you're point about some developers being intimidated about changing uncommented code because it's in production is interesting and true. I suppose this problem could often be a symptom of a team that does not have confidence in its regression tests.
Tom, I think you misunderstood what I meant when I was blathering on about patterns. I know you weren't talking about them yourself in your original response - I just mentioned the fact that pairing on code results in a codebase of pretty uniform quality (and from what I can see the quality seems close to the quality of the strongest members of the team rather than the weakest). I just thought people might find this interesting.
Again, I didn't say 'no comments'! I just rejected it as being a commandment. I think saying 'always comment your code' is as silly as saying 'always avoid full table scans'. Comment difficult to understand code only when making the same code easy to understand is not possible.
Of course on any large project no single person will be an authority on all parts of the system. The good thing about XP is that you tend to end up with a team where everyone knows a little about a lot of the code, and knows a lot about a little bit of the code. We do actually intend sharing knowledge of the code 'by word of mouth from generation to generation'. Put like that of course it sounds stupid, but as I said in my last post there's no substitute for sitting side by side with someone and explaining what the code does while it's open in a text editor. I'd far sooner put in that investment when a new member joins the team (it's part of XP anyway) than drag a large body of documentation around after us as we continually change and upgrade the codebase. In my experience this sort of documentation is often read very rarely anyway.
At the end of the day, if we do come to a situation where we have to hand the entire codebase to an entirely new team (I'd say there's a less than 10% chance of this happening) We can write the documentation then. For sure it's harder to write documentation after the fact, but it's not 10 times harder, which it would have to be to merit us doing the documentation now if you look at the probabilities.
December 04, 2005 - 11:40 am UTC
... if the sql is too complicated for a developer with
reasonable sql knowledge to grasp the gist of it by reading the sql itself. ...
define reasonable :)
we will have to disagree, I think always commenting your code is a rule, not a rule of thumb.
The bad thing about XP (I was doing XP before XP was called XP - we called it "mentorship", "peer review" and many other things) is that teams come and teams go. The people you work with today will likely not be the same team in 3, 5, 7 years - but your code may well be around and may well not have been widely looked at. Coding standards change, what was best practice back then is laughed at now (speaking in the future tense). Styles change - I cringe when I look at some of my 15 year old code (all of the braces are in the wrong place after all....). (xp is not much new, it is as old as dirt, much of what is exspouses - good development teams were doing long ago - planning, DESIGNING, coding, testing...)
There is no substitute for peer review, agreed, that is what we used to call it years ago. But, the need to document remains. I've inherited "non documented, but hey you can read code can't you" code - the problem is the people inheriting the code typically didn't write it and don't know those that did.
As long as there is a chance, that is all that matters. 10% is hugely high. And no one would give you the opportunity to write the documentation at that opint.
we will have to agree to disagree on this point. heck, comments should be written before code in most cases. (used to be part of the design phase)
Changing development teams
Gary, December 04, 2005 - 6:18 pm UTC
"I also think it's not realistic that in 5 years time an entirely new set of developers will magically drop into the company and have to piece together the web we've weaved through the comments in the code"
Been there, done that. Call it off-shoring.
Can happen with mergers and takeovers, cost blowout on a project "Shelve it for six months until we get some more money...".
Comment the WHY and 'What I thought this code was going to do'
Andy Hardy, December 05, 2005 - 6:04 am UTC
I like to see comments in peoples code that indicates 'WHY' the code is there i.e. what requirement it's being written against and then a quick description of the method of fulfilling that requirement.
It's a lot easier to fix code if you have a clue as to what the code addressed and what method was being used.
It may be obvious from reading the code that 'X is being applied to all customers whose Y=Z', but WHY this is being done can be a lot more difficult to fathom???
Thanks for your answer on 22 nov.
Henk Boerboom, December 05, 2005 - 6:16 am UTC
Select "A man who holds a peerage by descent or appointment." Tom_Is From Dual;
@ A reader who reviewed on November 22, 2005
Henk Boerboom, December 05, 2005 - 6:47 am UTC
<<Georg Cantor mainly was interested in infinite sets.>>
Maybe Georg was interested in René Descartes ;-)
<<it suffices to think in sets the way pupils learnt to
in primary school about 30 years ago>>
The main reason I put it in, was that I have seen to many persons, who were Cobol-programmers, jump on PL/SQL after 6 weeks of Oracle training.
Q: what does peers mean in this context ?
An intentionally anonymous reader, December 05, 2005 - 9:01 am UTC
Within my organization, for SQL & PL/SQL programming, I have "no superiors and few equals". I have to come to asktom.oracle.com to find them !! :)
It is all about maintainability
Maarten Vinkhuyzen, December 05, 2005 - 11:26 am UTC
It is nice to see the discussion centre on the comment and the code. After years of maintaining code at dozens of corporations it is my experience that well written code without comments is a lot easier to understand / maintain than doggedly commented code that is of the standard cryptic c++/java style.
The truth is in the code! Never mind what the comment says, the computer will do what is in the code. So analyze it until you do understand it.
So, I always tell colleagueÂ’s to imagine the worst programmer they know. And that he will try to maintain the piece of code they have just written. And of course will break it. And then I tell them to rewrite it in so clear and self documenting a manner that even that programmer will not accidentally break it. And after that put some comments in to tell the goal the code is trying to accomplish. Not what, but why.
Starting with comments is something I often do. But those comments usually turn into the procedure names of the code that come after it.
Well written code that does not use bind variables is easily corrected. Brilliant, blazingly fast code that canÂ’t be deciphered will be discarded.
At the top of my list are:
1 Use procedures to name and decompose your code.
2 Use meaningful and unique names! Never code ‘for i in (1 .. 42)’ because somebody used i already somewhere else! (And besides, what is the meaning of i?)
3 Computer languages are LANGUAGES, so use them to tell precisely what the program does, don't use code.
Every body can write a program the computer understands, (=compiles) but writing a program your successor will understand, that is an art.
December 06, 2005 - 5:09 am UTC
I still want to know what the point of the code was, why are we doing what we are doing.
I can read code.
I can reverse engineer code.
I want to know "why" code exists in the first place - and I don't want to have to be part of an extended family that passes down knowledge generation to generation by word of mouth to get it :)
You see, I'm called in to look at other peoples code and I quite simply don't have the time to become "one with the system".
Why, Why this way Why not that solution, please.
Maarten Vinkhuyzen, December 06, 2005 - 6:45 am UTC
<<I can read code.>>
<<I can reverse engineer code.>>
You can even write good code, brilliantly so.
But I come in when the extended family with its singsong stories about the code and what became of it is long extinct.
I often feel like an archaeologist.
Most comments just exist because of the rule "Thou shalt comment all thy code." and just repeats (vaguely) what is much clearer stated in computer language at the beginning of the line. Like:
amount_sold := amount_sold + price_item_sold -- add sales.
Frequently the code is maintained but the comments are not.
But sometimes there is a gem, comment that tells not what the code does, but why it is this way. What this programmer is trying to accomplish. The best is with the pro and cons of the chosen solution and the considered alternatives. These comments are invaluable.
Adding this kind of comment, do, do, do.
But in practice, I often format the code and strip the comments(after reading them) in order to understand it.
I don't think there is much difference (if any) in the comments we wish to find in the programs we are called in to maintain.
But I am afraid my attitude is a bit tainted because of the comments I usually find.
P.S.
And please continue teaching us to code simple and effective.
December 06, 2005 - 8:18 am UTC
then the coding standards are not being followed are they.
If you have coding standards
and you are saying the coding standards are being enforced
and the golden rule of the coding standards is "thou shall keep it all up to date, not just the bits that interest you"
and you are using "XP" - so you have this peer pressure thing going....
then no problem at all eh?
Seems that we were preaching the value of coding standards and how that made the code so intuitive and all - well, this is just part of that. If you have intuitive code "de-facto" because of this approach, you have comments that are up to date, useful, meaningful as well :)
You cannot have one without the other.
WHY vs HOW
Duke Ganote, December 06, 2005 - 9:05 am UTC
> I want to know "why" code exists in the first place
Exactly. This, particularly in XP, is vital because the WHY is less like to be recorded. There must be a clear distinction between WHY we're doing it and HOW we're doing it. Oracle provides many marvelous options for HOW to accomplish a task. But understanding the WHY, the business purposes (perhaps decomposing or abstracting into some technical purpose) is vital. Tom wrote at
</code>
http://asktom.oracle.com/pls/ask/f?p=4950:61:9056620297131730336::::P61_ID:5500484045890#47352208686203 <code>
> this is a concept I bring up in some of my seminars.
> I show a piece of procedural code
>
> Then I write out what it does.
>
> Then I explain how the customer I was benchmarking with
> [exclaims in response] "it doesn't do that"
>
> And I have to show them it did.
>
> Then they realized they had a bug.
A litle confusion, no xp here.
Maarten Vinkhuyzen, December 06, 2005 - 9:20 am UTC
Dear Tom,
I am not the XP guy. I just come in after the bright XP guys have gone to their next project.
I am not opposed to documentation.
It is nice to have a knowledgeable person explain the code, but mostly there is nobody.
It is just that I am used to (specialized in) maintaining old code. Often with tons of paper documentation and useless comments of the type mentioned above.
In my experience well written code is more important then code littered with comments. Too often I encounter a main procedure a 1000 lines long. Consisting of 1 big multy nested IF statement. Even with comments, you have to rewrite it before you can start changing it.
A program should be easy to read and to understand. Illuminated with comments where you can not get the code self explaining enough. But the first effort should be in telling the story of the program in pl/sql or java or cobol or whatever you are using. Don't use comments in stead of clear code.
To rephrase the 1 commandment:
"Thou shalt use comment to clarify WHY you have written that."
But for me, it will always come third. After naming and structuring. And always remember to KISS. (not meant personally)
I think the reason I react in this thread is that these are simple rules, simple to check and often simply followed. Too often I am told that these are good programs because they comply with all the rules.
But quality does not come from following simple rules. Good programs are written like good books. Rewrite the text again and again after review of your peers. Until the text is not only correct, but easy to understand as well.
U know, U write good books. (U as the dutch polite form of you)
December 06, 2005 - 9:49 am UTC
... But quality does not come from following simple rules. ..
nicely said.
Sad, but true (?)
Bob B, December 06, 2005 - 2:41 pm UTC
At the end of the day, XP isn't going to comment your code. Java isn't going to code your business logic. OOP isn't going to explain what the code should do and how it attempts to do it. These are all *responsibilities* of the programmer.
Newer and better tools do *not* obviate the need to design, code, and test what you write. New tools can make life easier, but the quality of the code is going to depend almost entirely on the neanderthal at the other end of the keyboard.
comment on comment
Denis, December 06, 2005 - 6:01 pm UTC
-- define reasonable :)
I know your tongue is firmly in your cheek for this one Tom! Of course the very fact that we are required to 'define reasonable' so often in every working day as part of our job is one of the best things about being a developer. We all have to make quite important judgements on our software every day.
I definitely agree that the most useful comments will enlighten the reason why a decision was taken to code something in one way rather perhaps in a different (more obvious) way.
Who would rather have code like
def process_good_employee(employee):
#give employee pay rise
employee['pay-rise'] += 100
#give employee promotion, he's a top quality dogsbody
employee['position'] = 'dogsbody + 1'
etc. etc.
than
def process_good_employee(employee):
give_employee_pay_rise(employee)
promote_employee(employee)
I know this is a brain-dead example but it's extensible to most code. If your commandment was 'comment code', you'd be happy with the first example, however if you forced yourself to go a step further you end up with few comments, but nice code.
This is a simple idea but of course the second approach has the enormous benefit of being easy to unit test - even though the second sample has no comments I'd much prefer to encounter it than the first (commented) code.
Anyway, if you write tests first you end up with code like the second style by default, because anything more complicated is simply too hard to test in isolation.
December 07, 2005 - 1:44 am UTC
I would rather have code like this:
/*
* this routine is to process good employees
it was created on this date xxxxxxx by yyyyyy
modification history:
by sxsssss on yyyyy (fixed ... )
by afddfafda on afdfds (fixed ... )
...
what it does:
this routine is all about implementing the legal standard X.Y.Z
enacted in 2002. The process in general is as follows:
a)
b)
c)
the flow of the routine is as such
......
etc
my inputs are...... because .....
and i produce as output this........
*/
process_good_employee
.... code with meaningful comments when necessary here
....
/* we are implemented the standard X.Y.Z which says we must
process these conditions referenced in document y in this
order in order to determine the correct raise amount
*/
if ( cond1 )
{
...
}
elsif ( cond2 )
......
and so on.
No, I would not comment "the bloody obvious" :), I would comment that logic - not a function call or single line of code.
It is not about being pedantic, it is about stating the intention.
Of course useless comments are useless
Martin Burbridge, December 06, 2005 - 9:54 pm UTC
But not all comments are useless, like the ones in the previous example.
A useful commect I would like to see
select e.ename, e.sal, d.dname
from emp e, dept d
where e.deptno = d.deptno (+);
Why the outer join?
Modification history in comment header
Grant, December 07, 2005 - 9:48 am UTC
> * this routine is to process good employees
> it was created on this date xxxxxxx by yyyyyy
> modification history:
> by sxsssss on yyyyy (fixed ... )
> by afddfafda on afdfds (fixed ... )
> ...
Wouldn't it be better to exclude the modification history in the routine header in favor of putting it in some manner of source code control system? The modification notes would exist in the check in comments, but then you'd also have the ability to diff between the versions to see the changes themselves.
December 08, 2005 - 1:02 am UTC
depends on what tool you are using and many of them put that header there for you upon checkout.
Agreed
Denis, December 07, 2005 - 12:53 pm UTC
I was going to say the same thing myself re. modification history. We used to put it in the code, but using your SCM system to hold this information is much better, as not only can you get the change history but you can also get diffs between changes (at least with subversion you can). We actually use a tool called JIRA to log requests for changes from users.
You can point JIRA at your subversion server and it will match any code commits with raised issues, so that even the humble customer can get feedback on when actual work has been done on a particular issue/new feature that has been requested.
Anyway, I digress. Let me re-emphasise that I don't think comments are evil. Comments can be incredibly useful. To repeat what I said in my first post on the subject, the practice of test first development IMHO can lead to 'comments' in the form of tests.
In your example Tom you have the following (very informative) comment:
/*
this routine is all about implementing the legal standard X.Y.Z
enacted in 2002. The process in general is as follows:
a)
b)
c)
*/
Now, I personally wouldn't comment like this. What I'd have is a Test class called TestProcessEmployeeImplementsIndustryStandardXYX() {
public final void testProcessImplementsStepA(){}
public final void testProcessImplementsStepB(){}
public final void testProcessImplementsStepC(){}
}
This is what I was getting at when I said well written tests can be a 'living comment'. Not only are you _saying_ what the code should do, but you are also actively _asserting_ it.
As far as classifying inputs and outputs the same logic applies - have
public final void testXEmployeeInputResultsInYOutput(){}
and so on and so forth.
Of course the test case can be wrong, but if you're coding test first and are not writing code for which you have no test then this is comparatively rare.
December 08, 2005 - 1:37 am UTC
we'll have to continue to agree to disagree. I hate that package name. I like readable stuff, with correct grammar, punctuation, human readable stuff.
David Aldridge http://oraclesponge.blogspot.com, December 08, 2005 - 10:25 am UTC
If the name of the legal standard changed, or the legal basis for the requirement was latered, then a naming convention of "TestProcessEmployeeImplementsIndustryStandardXYX()" could be something of a problem
uncommented code
nowhere man, December 10, 2005 - 1:40 am UTC
I was watching a manager attempt to figure out what was wrong with some code in an assembler-style proprietary language, with no syntax manual and no comments. After a while, I just couldn't watch anymore.
If there were comments it might have been bearable to watch, maybe even hackable (I worked on a predecessor to the language 15 years ago, but don't remember a thing).
The syntax manual comes with the optional development system for tens of thousands of dollars.
All this just to add a couple of fields to the XML that feeds the Oracle db.
Depends what you're used to
Denis, December 11, 2005 - 10:16 am UTC
I suppose it depends what you're used to. The camel case used in my example would read quite naturally for a java developer with any experience, and the availability of the test code as well as test input and output would convey more meaning to an experienced developer than any comment. Anyway you've already said that we should take unit testing as a given, so you obviously agree that the test cases like the example I gave should be written (I hope I'm not putting words in your mouth here).
So the difference in our opinion (correct me if I've got this wrong) seems to boil down to the fact that I think a combination of well written, clear and comprehensive unit tests, along with a policy of writing simple, well laid out code (commenting where this is not possible or desireable) is enough to ensure that future developers can fairly easily pick up an understanding of the code. You think that not only this is needed, but also a policy of comprehensively commenting all units of code is required.
BTW, your books are great - an idea from a section on function based indexes solved some tricky data integrity issues in our DB.
December 11, 2005 - 5:25 pm UTC
I am an experienced developer.
I would not be able to intuit your system from a bunch of unrelated test cases. (and yes, we've been doing this thing called "unit test" for many many years - right before integration testing....)
I think not only is that needed,
but commenting the code is needed,
And maintaining documentation is necessary to boot. ;)
Writing Quality Code: Following commandments or responsibly authoring it
Anthony Harper, December 11, 2005 - 9:48 pm UTC
These 'commandments' seem to be actually too specific, in so doing they may miss the mark if the purpose is to help the organization raise awareness about writing better code. Since most of the followup to this list ended up as a discussion of the merits of comments and an interpretation of a 'school' of thought perhaps these 'commandments' did fail to raise awareness of how to write good, maintainable code for the current requirements and for future programmers to maintain.
In thinking about this issue to help the organization that I am working at currently (and the future developers at that company) I always return to that old slogan 'THINK'.....and my interpretation of it:
Think about how the code should be written to fulfill the business requirements you have been given.
Write the code to fulfill your design.
Review what you've written.
Refactor what you've written.
Review the code to see if it actually functions as you expected it to.
Repeat the process from the beginning, using your first attempt as a draft.
This could also be simplified as the following:
Write code that clearly illustrates the business process.
Review and refactor the code until it actually performs as expected.
Review, revise and refactor the code until it is clear enough to have another developer understand it without additional verbal explanation.
Too often programmers dont take the time to review their own code from the top to the bottom. By the time you have finished a particular piece of code you will have a new perspective from which to review it. This new perspective in turn will help you to refactor the code to make it more clear and more reusable.
If we take the time to 'author' the code, we take on a responsibility to review, revise, refactor and edit the code. This time is significantly less than the amount of time it will take a future developer to rewrite the code in order to understand it for the purposes of making changes. If we take the time to review and revise the code when first writing it, we will have added comments, structure and explanations as needed to let the text 'speak for itself', or else we couldn't quite follow it during review.
If you are extremely dedicated to the 'Profession' of writing quality code: review, revise and refactor the code again until it is clear enough for a manager (ie, non programmer) to understand it without further ado. Part of the beauty of Oracle SQL and Oracle PL/SQL is the fact that it is written in clear English. This can be leveraged to make the code comprehensible to a non technical audience....if you can do this then you can 'kiss' the code goodbye, and leave it to fulfill its purpose until someone needs to revisit it again when its original purposes have changed.
I'm not sure we need to have 'commandments' that must be dogmatically adhered to, or 'schools of thought' (ie XP) that use a currently popular style or metaphor to drive our development processes. I am sure that we all need to take the extra time to 'author' the code so that it is something to be proud of, and so that it is something that could serve as a textbook example of the business process that the code has been written to carry out. The 'ROI' on this extra editing and review time up front will always outweigh the costs of future maintenance.
Another successful strategy is to remind developers to write code that will be understandable and clear when debugging at 6:30 pm on Friday when there is a production problem. If the code is not clear, if it is not clearly structured and broken into unit testable components, you will not get out of the office until 9 pm. When the heat is on and you are stepping through the code, you will thank yourself and your predecessors for taking the time to make the code clear and easy to understand.
PS. I did review this letter three times and I am sure I have missed something and it will not be entirely clear to everyone. Please help to make it a better review with your feedback.
December 12, 2005 - 7:56 am UTC
I THINK sometimes bulleted lists as above are useful.
tho shalt INSTRUMENT your code.
for example - a directive.
Your list is good - for the expert. For the novice, what does "write the code to fulfill your design" mean exactly?
If you are the novice, or just the average/below average - to what end will reviewing your code be? If you haven't instrumented it, commented it, benchmarked it, stressed it, tested it, etc. If you don't know bulk processing is "good", and you review your slow by slow code - what will you detect other than the code must be good?
So, I think I still for the most part like this particular list. Could it be expanded - sure, almost infinitely large probably and at various levels of "dirction".
comments
karthick, December 12, 2005 - 8:41 am UTC
"Thou shalt write the code and then comment"
Or
"Thou shal comment and then write the code"
most of them do the first one but i think the second one only makes sense.
A reader, December 12, 2005 - 1:03 pm UTC
Ruminations of a project manager (big consulting company):
The first commandment is:
Thou shalt deliver on time
For remaining 9, repeat the first commandment 9 times. Comments-> what a waste of precious coding time. Anyways, did it say in the contract that code will be commented? Probably not. If later it turns out that it did, we will put something in. If I don't turn the code in by <insert date here>, the client will stop the payment. Then I will have some explaining to do. Wish the users would somehow sign off on that useless acceptance test. I have only ten days left to fix bugs and roll this thing out. Must make these contractors work 14 hours. Maybe call them on weekend also. Fire anyone who says no. Can't afford any delays. Hmmm what else is there? A performance test? Sure, we will give them a performance test. Does it say we have to fix something based on performance test....
December 12, 2005 - 2:17 pm UTC
Thou shalt deliver no code before it's time and if that means that the project manager must actually fess up early in the cycle that the projections for the project were overly optimistic and "eat crow" as a result, so be it.
That could happen too.
fess up early?
An intentionally anonymous reader, December 14, 2005 - 9:53 am UTC
> Thou shalt deliver no code before it's time
> and if that means that the project manager must
> actually fess up early in the cycle that the
> projections for the project were overly optimistic
> and "eat crow" as a result, so be it.
>
> That could happen too.
And pigs could fly. Usually deadlines are imposed from above (omniscience, I figure). Details are shaken out after deployment as patch releases or are partially covered up by deadline extensions as requirements change.
Even when I contribute to "bottom up" estimates, I'm not infallible, and any mistakes are expected to come out of my family life. Every system is pretty much "one-off" not cookie-cutter.
The only thing I ever saw work with a project manager was this: when I'd gave him an estimate, he'd immediately come back with "well, that's probably padded; we can shave 30% off, right?". To which I'd answer: "no, I'm usually optimistic, and it may take 30% longer than my estimate."
I was never a well favored developer...
A few more suggestions
Rob Vollman, December 19, 2005 - 7:32 pm UTC
Great thread! I blogged on this:
</code>
http://thinkoracle.blogspot.com/2005/12/20-plsql-coding-tips.html <code>
I incorporated a lot of the feedback I read here in the original 10.
Here are the extra ones I added:
- set up a proper testing/development/debugging environment
- use source control
- choose the right tools
- write test cases before coding
- check and handle errors
I also "violently agree" with Tom on using comments. Comments are no substitute for making your code readable, but then again, make your code readable is not substitute for comments!
If someone is reading your code, it is for three reasons, usually.
1. He is trying to fix a bug
2. He is trying to write an enhancement
3. He is trying to re-use it
ANTICIPATE his needs, and write your comments appropriately.
I also really like promoting all the work that should be done before programming, but in reality most of that is fantasy: coding starts on day two.
Oh, and I definitely like the idea of promoting code instrumentation. If you like that topic, read Mogens and a few others in the "Tales of the Oak Table" book. Good instrumentation is one of the things that takes a "good" program to the "great" level. (and a "horrible" program to "useable").
to: "An intentionally anonymous reader"
cosmin, January 12, 2006 - 11:47 pm UTC
nice article. ;-)
...although, in my experience, deadlines are all too often imposed by sales people who have very little, if anything, in common with technology. Moreso, "technical specifications" are typically given by their assistants, after the sales people have become the heroes of the company for meeting a presupposed sell "quota", only to leave the developers sweating bullets fighting a deadline with improper or inadequate specs.
The problem these days seems that quantity seems of a bigger importance vs. quality. It seems that developers and key technical people are expended of, or going through a ridiculous turnover, in the name of profit. There used to be different times...but times are a-changing, as so very well put in Alvin Toffler's classic "The Future Shock".
Companies, in my oppinion, would be much better served if they put extra & consistent effort in keeping, motivating, encouraging, challenging and growing existing **tallent**.
A favorite saying is -- which seems to be the mentality these days -- "yeah, sure, I need this done, and oh, yeah, I need it done yesterday... but the specs, ...oh, ...the specs .... umm... they won't be ready until tomorrow".
...and so, the pigs started flying...
to Rob Vollman
Cosmin, January 12, 2006 - 11:51 pm UTC
nice blog.
Good expansion (in the blog) on the original ideas ;-)
don't mind me if I incorporate some of your finetuning thoughts ;-)
great job, Rob! (rhymes too)
Speaking of tools...
Dan Loomis, May 24, 2006 - 4:08 pm UTC
...are there any out there that help *enforce* these commandments? Quest's FormatterPlus is the closest thing I've seen, but it's not very extensible and is not able to run on the backend. I'd like to come up with our own "top 10 list", enter the rules into the product, profile the code, and report on the violations.
Anyone know of such an application?
commandments
sam, July 04, 2011 - 2:06 pm UTC
Tom:
Nice blog.
WOuld you say that one commandment should be to use a version control system with oracle development?
Also, i am curious would any VCS work with oracle since the code is not file system based like others.
I am curious on what tolls you use (centralized VCS or distributed VCS) and whether Subversion (CCVS) or git (DCVS) would bot hwork with oracle code.
thanks
July 05, 2011 - 11:56 am UTC
use version control with development would be a better way to say it. Doesn't matter what sort of development you are doing...
Any source code control can work with the database - not sure what "not file based" would do for you?
Oracle has their own developed source code control - we don't necessarily use an off the shelf offering for that. Various groups use different tools sometimes though - I know subversion has been used for some projects - it all depends.
Comment Code
Jim, July 05, 2011 - 12:34 pm UTC
Please include in your commented code things you tried and rejected and why. (just a short note) Too often I have seen people reinvent the wheel and redo things that have already been tried. Also comments should say what the code is supposed to do. The code may actually do something else. It is great to have unit tests and test cases etc. However, often there is a misunderstanding between requirements and execution. (no blame storming, we are talking communication and there are opportunities to misunderstand. It happens even in the best shops.)
vcs
sam, July 05, 2011 - 9:32 pm UTC
TOm:
<<Any source code control can work with the database>>
Does your statement apply to Distributed Version control system like git.
A DVCS (unlike CVCS - centralized) will clone the version control DB and download it to the developer client machine where you do the changes and then merge it back to the server.
I personally do not see how it works. Let us say you have 3 developers. They want to edit same package.
Should not you lock it for the first developer until he is done with his changes?
Also, oracle compiler is on the server. We always have to push the code back to server for compilation. Does the VCS keeps a copy of every compile or only when you check it back into the VCS. I am not very clear on how it works.
July 06, 2011 - 8:00 am UTC
you have code
code is code
database code is code
since code is code and database code is just code and you have code
any source code control system you like to use it ok.
Let me change this paragraph of yours:
I personally do not see how it works. Let us say you have 3 developers. They
want to edit same source code file.
Should not you lock it for the first developer until he is done with his
changes?
why is it unique or different if it says "package" instead of "source code file"?
also, let me rewrite this:
Also, the compiler is on the OS. We always have to push the code back to
OS in the form of a file or something for compilation. Does the VCS keeps a copy of every compile or only when
you check it back into the VCS. I am not very clear on how it works.
It is just like checking out source code, unplugging from the network and working on it. You check it out, you check it in.
Here's a dull but important one
Dave, July 06, 2011 - 6:04 pm UTC
Thou shalt remove unreferenced code at every opportunity.
Might not seem that important during initial development but 2, 3 ... 10 years down the line when you realise that you've been pulling your hair out for a day trying to keep alive some functionality that nothing uses, you'll then learn the importance of this.
Open a piece of source code that hasn't been touched for a while and the first thing I do spend a couple of minutes chucking out all the clutter. It's very cathartic.
vcs
sam, July 06, 2011 - 10:10 pm UTC
<<It is just like checking out source code, unplugging from the network and working on it. You check it out, you check it in. >>
What about your concurrency controls?
Think of 3 people checking out a database record and all updating it at the same time. if you do not do optimistic or pessimistic locks your database is inaccurate, you have serious problems.
same situation here. how can 3 developers work independently and then push the code back. it might not compile when you merge the changes even if the merge was valid. let us I and you updated the same routine. which changes are valid now.
code is code which is text. But I think code stored on the filesystem is different than code stored inside the database.
July 08, 2011 - 2:21 pm UTC
Sam,
come on. If you check out code - for updating - can anyone else check out that code from the same branch if you want to lock it?
This is one of the reasons you use source code control in the first place.
If you have three developers working in the same branch, only one of them get checkout for modification.
Or, they can all work on the same source code file - it gets merged together when they check in.
The source code is stored in source code control - it isn't stored in the file system, it isn't stored in the database - the master copy, the copy that wins, the official copy is in source code control.
Do you use source code control? This is pretty basic isn't it?
What about your concurrency controls
Oleksandr Alesinskyy, July 07, 2011 - 2:57 am UTC
<<It is just like checking out source code, unplugging from the network and working on it. You
check it out, you check it in. >>
What about your concurrency controls?
Think of 3 people checking out a database record
No difference from a file-based system. Practically none of the modern version control systems uses locking (at least as default). CVS, SVN, GIT - name it yourself. And it absolutely does not matter is a system called "distributed" or not. Anyhow developers modify their own working copies and several developers may make concurrent changes in the same file. "Distributed" systems just add an additional level(s) of merging (in a non-distributed system you merge from your working copy to the repository, in a distributed system you merge from the working copy to an intermediate repository, then somebody merges an from an intermediate repository to the central repository - so you have more merging, but that's all, no ground-breaking changes).
Bill Watson, July 07, 2011 - 10:53 pm UTC
My list, for our developers writing SQL statements, based on common things done wrong:
1. Fully qualify and specify column names
2. Select only the data you need
3. Use bulk SQL operations
4. Use bind variables*
5. Build SQL dynamically when the WHERE clause changes
6. Avoid functions/calculations in the WHERE clause
7. Use ORDER BY if you want consistant answers
* There is further advise about when to break this rule which in our case allows Oracle
to make a better plan for skewed data partitioned by the skewed column.
July 08, 2011 - 2:39 pm UTC
I would caveat (5) - there are times when you want to do that, there are times when you do not - if you can accomplish your goal with two or three queries - I would lean towards static sql in plsql before going dynamic.
I would change (6) to be "avoid if possible applying functions to database columns in the where clause". where hiredate >= sysdate-7 is great, where hiredate+7 >= sysdate is not so great. Expressions are not so bad, it is the expression needlessly applied to a column.
I would change (7) to say "Use order by if you want data sorted". We always give you a consistent result set - it just might be in different orders if you do not use order by :)
Otherwise, great list.
source control
A reader, July 09, 2011 - 11:41 pm UTC
Tom:
No, I dont use source control. I used PVCS (server VCS) once before with other developers who created some nightmares (not due to it) and they all left and now it is down to one developer.
With one developer, you can just copy the files every day to a local directory on the filesystem or server. I personally do not like the check out/check in. I never had to go back and pull out older version of something.
With DVCS there is NO checkin/checkout. The whole VCS database is imaged and copied to your client. There is no locking at all. Everyone could be updating the same SP at same time. I need to read more on how MERGE works when all developers sync back their changes back to the central server.
July 12, 2011 - 7:12 am UTC
Sam, you need to reconsider your position and your thinking on this. You probably need to get into an environment where you are not the sole developer (you are not going to progress very far nor very fast on your own).
If I was called in to inherit your code base, I would probably want to leave on the first day (I haven't see it, but I'm pretty sure). the reason: No change history, no record, no reasoning why things are done the way they are, no commentary, no timelines, nothing - nothing but the current state of being (undocumented - I know that too because you've asked dozens if not hundreds of questions over the years here that indicate that to be true - such as "how can I find out what tables are not being used anymore" and the like - scary scary stuff).
You are the reason source code control was invented.
@Bill Watson
Oleksandr Alesinskyy, July 13, 2011 - 3:19 am UTC
1. Fully qualify and specify column names
I hardly may understand why this item is at the top of your list. IMHO it much less important than any other.
July 13, 2011 - 8:11 pm UTC
that is a really important one in real life. I like it being at the top.
If I had a nickle for every time someone asked me:
is this a bug in oracle, why does this query work???
select * from emp where empno in (select empno from dept);
there is no empno column in dept!!!!! how does this work????
if they qualified columns - always - it would not be confusing at all.
also, it included specify - which means:
select * ....
insert into t values ....
would not be permitted in production code - which again is perfect.
I'm a fan of it for production code.
Alexander, July 14, 2011 - 8:51 am UTC
I'm going to own you 5 cents.
I still find that to be very unintuitive. Even fully qualified, in English it still reads "select this column that doesn't exist in dept from dept". Also, what is the use of comparing empno to itself in the same table? I still consider myself a novice at many things in Oracle. This is just one of those things that was never explained to me and I just have to say, it is confusing.
July 15, 2011 - 9:08 am UTC
it is just called a correlated subquery.
If we coded:
select * from emp where emp.deptno in (select dept.deptno from dept where dept.deptno = emp.deptno)
you'd have no problem with it right?
so, why is:
select * from emp where emp.empno in (select emp.empno from dept);
so far off :) it is just a correlated subquery - all of the values of the current row of the outer query are available as inputs to the subquery.
Alexander, July 15, 2011 - 9:51 am UTC
Ok I think I misunderstood your example. Was your point to just demonstrate the fact that those empno rows are available in the inner query, and that example wasn't really "real world"?
I was thinking more, why would you code that, not so much "this is possible because..." and the dept table isn't relevant in the example, it could be dual?
July 18, 2011 - 9:43 am UTC
This is real world:
select * from emp where emp.deptno in (select dept.deptno from dept where dept.deptno = emp.deptno)
and from that, we can see that
select * from emp where emp.empno in (select emp.empno from dept);
uses the same "rules" of sql scope - hence, real world or not, it is totally acceptable syntax and behavior.
For the first query to work, the second must work.
The dept table is relevant - what if it were empty? Dual is not empty. So, it could return a different answer if you used dual.
Think "sets" :)
Oleksandr Alesinskyy, July 17, 2011 - 1:41 pm UTC
also, it included specify - which means:
select * ....
insert into t values ....
would not be permitted in production code - which again is perfect.
I may agree regarding insert into t values but select * is perfectly sensible and in many cases inquires much less maintenance. While it depends - there are situation when it is unsuitable.
July 18, 2011 - 10:36 am UTC
It is almost never a good idea for select *, it should not be allowed.
They added yet another reason why select * should never be used - consider:
ops$tkyte%ORA11GR2> create table t ( x int, y int, z int );
Table created.
ops$tkyte%ORA11GR2> create or replace procedure p1
2 as
3 begin
4 for x in (select x,y,z from t) loop null; end loop;
5 end;
6 /
Procedure created.
ops$tkyte%ORA11GR2> create or replace procedure p2
2 as
3 begin
4 for x in (select * from t) loop null; end loop;
5 end;
6 /
Procedure created.
ops$tkyte%ORA11GR2> select object_name, status from user_objects
2 where object_name in ( 'P1', 'P2' );
OBJECT_NAME STATUS
------------------------------ -------
P1 VALID
P2 VALID
ops$tkyte%ORA11GR2> alter table t add a int;
Table altered.
ops$tkyte%ORA11GR2> select object_name, status from user_objects
2 where object_name in ( 'P1', 'P2' );
OBJECT_NAME STATUS
------------------------------ -------
P1 VALID
P2 INVALID
column level dependencies - much less invalidation if you only refer to what you want.
Here is my (unshakeable, unchangeable) option on this:
a) select * is a performance issue. You should ONLY fetch that which you need. It takes more work to retrieve a full row - rather than just the columns you need.
b) select * is a performance issue. The optimizer cannot perform things like table elimination in the execution plan if you select every column. If you select JUST those columns you need, it can possibly remove entire tables/indexes from the plan.
c) select * is a performance issue. If you could have answered the query you MEANT to ask by selecting JUST the columns you needed - we may well have been able to remove a table access by index rowid.
d) select * is a MAINTENANCE issue. For all code outside of PL/SQL - select * is deadly. The best of the worst that can happen is the code "accidentally seems to work", but works on the wrong columns. The best that could happen is the code fails at runtime (pray for that to happen).
e) select * is a MAINTENANCE issue. All of a sudden - it would appear that every bit of code is dependent on that column - change impact analysis - harder than hard.
Just to name a few.
I do not think this one depends that much, select * should *not be allowed* in almost every case. If a new column is added - then all code that needs that new column needs to be inspected and modified to accommodate that change anyway (you are going to go to the code).
It is bad for performance.
It hinders maintenance.
It obscures documentation.
IF you are going to say something like " is perfectly sensible and in many cases inquires much less maintenance."
THEN
please back it up with facts
END IF
A reader, July 19, 2011 - 4:42 pm UTC
Excellent Sir!!!
select *
Oleksandr Alesinskyy, July 20, 2011 - 5:57 pm UTC
Do you have something against select * from dual?
Do you have something against select * in a code of tools that need to support data browsing (TOAD, SQL*Developers, name them yourself)?
There are many more cases when select * is reasonable - as well as many cases where it is not.
Now regarding "For all code outside of PL/SQL - select * is deadly.". I am sorry, but this is a pure nonsense - do you really, really believe that all code save PL/SQL addresses column by their positions and not names?
July 22, 2011 - 1:12 pm UTC
we were talking about PRODUCTION CODE Oleksandr - not about toad and sqlplus and junk like that.
we very clearly said "in production code"
Would select * from dual be in production code? No, it wouldn't, if it were- it should be flagged as a resource consuming bug that does nothing and needs to be removed.
Would toad be in your production code? hardly.
Now regarding "For all code outside of PL/SQL - select * is deadly.". I am sorry, but this is a pure nonsense - do you really, really believe that all code save PL/SQL addresses column by their positions and not names?
Here we'll have to agree to disagree. To me this is not pure nonsense, but COMMON SENSE.
Do you see the performance issues? Please say yes, I know you know your stuff - do you see the performance issues?
Do you see that there is A LOT OF CODE that references things by position? If you do not, you might need to get out more and work with more people - you are working in too closed of an environment with really good people.
Do you see that select * is a maintenance issue, not a thing to ease maintenance - but to make it harder (here I think we disagree). I am, after many many years, a huge - big - large fan of EXPLICIT everything. Anytime I've relied on defaults or 'implicit things' - I've gotten burned over the years.
select * from t is as bad as "select * from t where date_column = '01-jan-01'" as far as I'm concerned - it is right up there with when others then null. It is just pure laziness.
Oleksandr Alesinskyy, July 22, 2011 - 6:18 pm UTC
we were talking about PRODUCTION CODE Oleksandr - not about toad and sqlplus and junk like that.
The TOAD code is a PRODUCTION code for developers of the TOAD.
Would select * from dual be in production code? No, it wouldn't, if it were- it should be flagged as a resource consuming bug that does nothing and needs to be removed.
Absolutely true.
Would toad be in your production code? hardly.
TOAD - no, but I have developed a similar tool while ago.
Here we'll have to agree to disagree. To me this is not pure nonsense, but COMMON SENSE.
Why outside PL/SQL? Where is a crucial difference?
Do you see that there is A LOT OF CODE
Even A LOT OF does not constitute ALL. And performance issues are highly overestimated (normally, a difference is pretty minor if not negligible).
that references things by position? If you do not, you might need to get out more and work with more people - you are working in too closed of an environment with really good people.
That's true to some extent - I do not do consulting, while I have worked in many more than one place and country. People(mostly) are able and willing to be educated.
I am, after many many years, a huge - big - large fan of EXPLICIT everything. Anytime I've relied on defaults or 'implicit things' - I've gotten burned over the years.
I was - at some point in time, but not anymore.
select * from t is as bad as "select * from t where date_column = '01-jan-01'" as far as I'm concerned - it is right up there with when others then null.
Here I disagree - while when others then null (or even when other then logSomething) is rightful reason for a termination without notice, select * from t may be reasonable.
It is just pure laziness.
Sure - but laziness is an engine of progress (famous Russian saying).
July 27, 2011 - 7:30 pm UTC
we were talking about PRODUCTION CODE Oleksandr - not about toad and sqlplus and junk like that.
The TOAD code is a PRODUCTION code for developers of the TOAD.
right and if the CODE for TOAD included select * - they have a problem. If you type select * into TOAD - did you just write production code? I think not. Be realistic.
Why outside PL/SQL? Where is a crucial difference?
in plsql,
for x in (select * from t)
declare
l_rec t%rowtype;
begin
select * into l-rec from t where...
fix themselves, not so in all other languages.
Do you see that there is A LOT OF CODE
Even A LOT OF does not constitute ALL. And performance issues are highly overestimated (normally, a difference is pretty minor if not negligible).
I cannot find any reason, short of laziness, to condone select *
I can only find a host of reasons to say "do not do that"
Oleksandr Alesinskyy, August 03, 2011 - 8:48 am UTC
>>The TOAD code is a PRODUCTION code for developers of the TOAD.
right and if the CODE for TOAD included select * - they have a problem.
Even in data grids showing all table/view data?
August 03, 2011 - 9:52 am UTC
toad doesn't have that select *, toad was given that select * by you - at runtime.
The code that toad ships with shouldn't have any select *'s in it.
That toad, like sqlplus, at runtime executes a select * isn't the point, never was the point. Those queries are NOT part of toad/sqlplus, they are input to them.
Avoid Bind Variables at all times - J Lewis
Mark Polsgrove, August 09, 2011 - 5:12 pm UTC
In a DSS system, you usually need to avoid bind variables at all times ... so people don't share execution plans for SQL that looks the same but performs enormously different amounts of work. Jonathan Lewis, Cost-Based Oracle Fundamentals (See Ch 3, in grey box on Bind Variable Peeking)
Example: Potential view in one of our DSS reporting tools used to help the end users join two datamart tables on Primary Keys.
SELECT a.col1, a.col2, a.col3, b.col1, b.col2, b.col3
FROM Rx_hist a INNER JOIN
Drug_hist b
ON a.phrm_pk = b.phrm_pk
where a.phrm_pk between :1 and :2;
Using dynamic SQL in place of Bind variables I can get the following plans:
--- 1 to 64 Records Nested Loop.
--- 64 to 500 recs HASH Join w/ access by Index / rowid.
--- 500 to 400,000 recs - Merge Join w/2 Full Table Scans
--- 400,000+ recs - HASH Join w/2 Full Table Scans
What is the best query for everyone to use?
I heard a story from Ric Van Dyke of HOTSOS about a system that always runs slow on Tuesdays when it rains... One guy rides his bicycle into the office every day. But, if it rains, he drives in and gets there early. Which means that he is the first user to run his weekly reports using Bind Variables. And, the optimizer peeks at just his Bind Variables to get an execution plan that works poorly for everyone else all day long.
-- thanks Tom, I have all your books and read them often.
p.s. I could use a couple more.
August 13, 2011 - 4:12 pm UTC
hahaha, the rain story is mine. My tell on it is:
on sunday we do a cold backup, shared pool is empty.
On mondays - all sql is freshly parsed.
when it doesn't rain, I come in early - I get my binds parsed. You come in late - you use my plan.
when it does rain, I go back to bed - I live in Washington DC, if I leave at 6am, I get to work at noon, 7am - get in at noon, and so on. So, I sleep in - you come in first. I use your plan.
Your plan doesn't work for me so good - rain is bad for my queries apparently...
and I don't see how:
0) thou shalt have a really good reason for NOT using a bind variable, binds
should be the default - concatenating in literals - the rare exception.
disagrees with the premise that you don't always bind. It would be the exception, not the rule, to not use binds.
It all comes down to math for me.
If you have a statement that is executed many times per second (more the norm) - then bind are a must.
If you have a statement that is executed infrequently and takes many seconds - then binds might be wrong for you.
Rules and Exceptions
Mark Polsgrove, August 17, 2011 - 4:33 pm UTC
One man's exceptions are another man's rules.
... By the way, thank you for teaching us that always and never are usually the wrong words when speaking in general terms.
When interviewing for junior DBA's to work on a VLDB, 10 out of 10 candidates knew that nested loops were good and hash joins were bad. So, we hired a senior DBA instead.
Coding standards and best practices are almost always good.
When are you coming back to Michigan?
search and replace
sam, April 25, 2013 - 5:02 pm UTC
Tom:
is oracle 11g RDBMS, is there a way to do global search and replace for a PL/SQL string for all the source code in a schema.
I have hundreds of procedures/packages and i need to do search for "
https://www/xyz.com" and replace it with a global variable.
Doing it for each procedure/package take forever.
I have SQL Navigator, TOAD, SQL Developer and SQL*Plus tools.
PLease advise.
April 25, 2013 - 7:56 pm UTC
sam,
read about sed.
very easy to do this at the unix command line.