Writing Good SQL -- Example
Have a quick browse through this thread.
The solution has it's own problem -- using a CASE expression to workout the earlier of two dates is too complex. As I wrote here, "There are around 170+ built-in SQL functions in Oracle 9i. You don't have to learn them all, but you do need to know which ones exist. If you think that a particular built-in function ought to exist then it probably already does."
The required function is LEAST(). Not only would the intent of the SQL be more clear and the code more concise, I bet that the performance difference could be benchmarked also. As a rule-of-thumb* the built-in functions are going to be faster than anything that you jury rig to do the same thing.
* I think this is my first mention of RoT. Hooray!
posted by David Aldridge @ 12:00 AM
2 Comments:
Yeah, I see such code now and then. I have seen people write there own NVL functions, getting a max value in a cursor loop, I've even seen code that used functions to retrieve data in related tables instead of just joining them.
Usually this code is written by people that think of themselves as very smart and they don't need to read manuals because of that :-)
Martin
"As a rule of thumb..."
Well, either you had a big fever or you had drunk too many beers with DaPi when you wrote these words. ;-)
Of course, i agree with you, and with Martin. Personally, when I want to do something that would be a little "complex" in standard SQL, the first thing I do (well, generally :-)) is going to the SQL Reference to see if Oracle does not already have a function that will do what I want, or greatly help me doing so. Generally it is of great help. From what I see on forums, it's crazy how few people actually read the documentation, while the ROI of doing so is incredible. In the end it is a gain of time (yes it is) and it more than often results in much better performance.
Post a Comment
<< Home