why can't people understand date arithmetic ?
AND TO_NUMBER(TO_CHAR(appt_start_dt + 9/24, 'YYYYMMDDHH24MISS'))
<= TO_NUMBER(TO_CHAR((TO_DATE(:b1, 'DD/MM/YYYY HH24:MI:SS')), 'YYYYMMDDHH24MISS'))
Please can a clever Oracle person explain, in English, the precise semantics of this WHERE clause snippet in the comments below.
I realise this construct and variants thereof have probably been discussed ad infinitum on Oracle WTF but I just don't get it.
I don't know whether this SQL was written by a human being or a third party ETL tool but it doesn't matter. Currently, all ETL tools are written by humans in any case.
Why do people struggle so much with date arithmetic ?
When I was a little boy, I used to say 'Only 43 days to my birthday now, Dad'. Although I didn't know it at the time I could have written this as
sysdate + 43 = :my_birthday
I even understand that TRUNC(SYSDATE) is midnight - it just seems fairly intuitive and logical to me.
My tiny brain can even understand the concept of date subtraction - last Wednesday was two days ago and Manchester City's last trophy was almost 34 years ago.
I understand that a date is a date is a date. I realise that a date is not an orange. I realise that a date is not a string and I only see dates in SQL Developer because that was a design decision to perform an implied conversion using the default date format and a useful user friendly feature.
I understand that if I want to display the date and be assured of the date format I need to convert it to a string using TO_CHAR with the appropriate date format mask.
I understand that Unix systems (and the next generation blogging platform, Habari) stores 'dates' internally as the number of seconds that have elapsed since the Unix epoch on 01 January 1970 00:00:00.
But look again at that code snippet. The author is obviously familiar with the Oracle functions TO_CHAR and TO_NUMBER but why in the name of God does he convert a date to a string to a number and then compare the resulting outputs ?
What is the meaning of that generated 'number' - 20100129143559 ? 'Twenty billion, one hundred thousand and and one hundred and twenty nine million, one hundred and forty three thousand and five hundred and fifty nine. Or something. Why is that a useful number ?
Why did he waste time and energy doing do all that typing ? Is he working on a top secret defence project that requires him to obfuscate the code ? Maybe he is paid by lines of code ? The author is clearly aware of some elements of date arithmetic in Oracle because he used '+ 9/24' to add 9 hours to the appointment start date.
Maybe there was a useful index defined on APPT_START_DT that he wanted to suppress for performance reasons.
I'm not sure but I have an awful suspicion that the object associated with the bind variable :B1 may already be declared as a DATE data type but hey, let's convert it to a date using TO_DATE - just to be sure, to be sure.
Why didn't he simply use
AND appt_start_dt + 9/24 <= [TO_DATE] :B1
Why ?
Steve Jobs on design
‘Make it look good!’ That’s not what we think design is. It’s not just what it looks like and feels like. Design is how it works.” — Steve Jobs
So very true. Via a comment on an interesting blog post comparing Tumblr to Posterous.
Ironically, this short post would have been better suited to a one liner, throwaway post correctly labelled and nicely formatted as a quotation on a service like, well Posterous or err, Tumblr but I am trying to consolidate all my efforts into this blog.
the mechanics of IT
Last year, I visited a customer in Swindon. When I arrived at the office, I noticed some water dripping from the underside at the front of my car. I didn't have time to investigate or get my hands dirty so I subconsciously hoped it was just condensation from the air conditioning unit and tried to forget all about it.
After work, I managed to navigate Swindon's world famous roundabout of roundabouts and finally locate my hotel. When I parked, I noticed the temperature gauge was reading high - very high. Sure enough, I got out of the car to see steam pouring out of the bonnet and was greeted by the familiar smell of anti-freeze.
I still didn't want to get my hands dirty so I opened the bonnet and just stared at the problem, hoping that would miraculously fix it. Then I checked in to my room and rang the AA. I introduced myself to the operator as 'Hi - my name is Norman and I'm an alcoholic.'
The AA man duly arrived before 'The One Show' had even finished. We both stood looking at the engine knowingly before he asked: 'Now, Sir, what seems to be the problem ?'
'Well, I think it's pretty minor and easy to fix. It's just the top hose has perished and needs replacing.'
'Oh I see, Sir. Why do you think that ?'
'Well, when the engine was running, steam was pouring out of the top corner of the radiator - right there where the water hose joins.'
'OK, Sir. Thanks for that. Please could you just turn the engine over for me so I can take a look myself ?'
Engine on. Water and steam billowing out. Smell of anti-freeze. Temperature gauge rising.
'Whoa ! That's fine, Sir. Engine off now, please. Well it's not your hose, Sir. The problem is over here. It's your bleed screw, Sir. Look (flips the screw from the middle of the radiator). This bleed screw has sheared off in half. Quite a common problem on this model. Seen it a couple of times now.'
I felt a little sheepish (but very relieved that a solution was in sight) and the man from the AA, James, filled the radiator with 13 pints of cold water and I followed him to a local garage where we parked and he kindly drove me back to the hotel so I could wash my hands.
The next morning, I walked into the customer's offices to be excitedly greeted with 'Oh good - glad you're here. We have got problems. Serious problems. Performance problems. On production.'
'Ah OK - what seems to be the problem ?'
'Well it's the database, you see. It's the hard disk - 99% busy. Partition 27b on logical volume 7 is overheating. Oh and another thing that might be relevant - the hit ratio is right down at 72.7%'
'OK - thanks for that. Do you think you could just start the system up for me and I'll take a look myself ?'
the importance of end users
Cary Millsap posts a brilliant article about his approach to performance troubleshooting that resonated loudly with me.
When I first started working at Siebel, a standard review was a production health check that consisted of meetings with key project staff (Siebel Administrator, DBA, systems and network admins, project manager, developers) coupled with some standard checks on key Web, Siebel and Oracle configuration parameters.
At the tail end of one engagement where I had precious little to note or report on, I asked to meet with an end user for a brief chat. The response was surprising and not dissimilar to the standard retorts Cary describes.
- 'Talk to a user ? Why on earth would you want to do that ?' (delivered with suspicious frown)
- 'The user won't understand your questions.' (patronising)
- 'The users don't understand the business requirements.' (surprising and worrying)
- 'The users are too busy.'
- 'Can't you just talk to a supervisor instead ?'
Of course, most of these responses are instinctive, defensive measures, immediate responses to what is probably a rather unusual request and, to be fair and in the interests of balance, I have been introduced to end users by development staff who do know and obviously have a healthy relationship with the user community.
If and when I succeeded in getting an audience with an actual user, I didn't actually chat at first. Instead I introduced myself and simply asked if I could watch the individual use the system for 10 minutes.
It was often very enlightening just to sit quietly and observe the business process (typically, handling an inbound call at a call centre), the subset of available screens accessed by the agent, typical searches executed to locate data, actions they expected to be slow, actions they needed to be fast, common tasks they did frequently, lengthy interactive queries that were truly batch reports as well as interactions with other applications.
why Linux will never succeed in the mainstream
I have been running Linux Mint for 8 weeks now and I've been delighted with it. My desktop PC is fast and responsive and I am hugely impressed by the sheer amount and quality of software available for Linux. Printing, scanning, wireless networking, audio, DVD writing and all my USB devices just work.
I don't have a virus scanner consuming memory and chewing clock cycles. I am no longer considering a memory upgrade as Linux works fine with my paltry 512MB.
I have all my favourite applications available (Picasa, Chrome, FileZilla, Emacs). Linux is brilliant as a development platform and installing software is easy. The Mint desktop looks great and with the addition of Microsoft TrueType fonts, my display is razor sharp and crystal clear.
Finally, and perhaps, most importantly, my wife has also embraced the change. She now uses Thunderbird instead of Outlook Express, Firefox instead of Internet Explorer, OpenOffice instead of Microsoft Word and Excel and Nautilus instead of Windows Explorer. All of this was fairly transparent and painless.
This fulsome praise all sounds like an advert for the wonders of freedom loving, a precursor for some open source software evangelism and a concerted attempt to convert the great unwashed to Linux. However, there is an elephant sitting in the room. Right there - in the corner.
The Ubuntu and Linux Mint (which is based on Ubuntu) distributions have a 6 month development cycle. This means that a new release will appear twice a year which is great because users know when the next major release is due. In addition, minor fixes, security patches and improvements are continually being pushed out via automatic updates. What is not so good is the actual process of upgrading to a major release which, in my opinion, is relatively complicated and risky for an inexperienced, new user.
To be fair, Linux Mint are upfront and honest and describe the upgrade process fully, the options available and the pros and cons of each approach.
'There is no guarantee that it will work for you. In fact, this [dist upgrade] is quite a risky process. If you’re experienced and if you know how to troubleshoot and solve common Linux problems then you’re probably OK. If you’re a novice user we recommend you perform a fresh installation of Linux Mint 8 instead.'
David Marsden is an experienced Linux user and comments that he is comfortable performing Ubuntu upgrades, quickly and reliably without losing his data. He claims that Ubuntu upgrades are quicker and easier than applying a Windows Service Pack.
Of course, David's absolutely right. Even I managed to upgrade to Linux Mint 8 at the first attempt without losing any of my user data and even managed to preserve the configuration settings for all my favourite applications. In fact, apart from the modified login screen and wallpaper, the four people who use the Linux computer would have struggled to notice the change, it was that transparent.
In fact, all I needed to do was:
- When originally installing Linux, create dedicated, separate partitions for user home directories and data. I use '/home' (user directories) and '/data' (music, photos, documents).
- Try to stick to the default Mint (and Ubuntu) software repositories.
- Note down the additional applications and software packages you have installed.
- Jot down user and group id's (copy '/etc/passwd' and '/etc/group').
- Backup the home and data file systems (twice). Check the numbers of files. Check the size of the directories. Check the checksums. Check the backups are readable. Check the hidden directories. Check the backup disk isn't full. Check everything.
- Burn the Install CD and install the 'upgrade'.
- Preserve the '/home' and '/data' file systems, leaving all existing data intact. You did remember to jot down that '/home' is '/dev/sda6' and '/data' is '/dev/sda8', didn't you ?
- Move '/home/user' to '/home/user.backup'. Repeat for each user account. This ensures that Gnome and desktop related settings are re-created.
- Re-create the necessary user accounts and ensure the user and group identifiers are the same as before.
- Selectively, copy the various, 'hidden' dot directories for applications (Rythmbox, Picasa, Pidgin) back into the user directory to preserve the application settings.
- Reconfigure wireless networking.
- Reconfigure the printer.
- Remove the irritating fortune cookie from 'Terminal' (Mint only).
Now I am fairly technical and understand most of this. I have no problem whatsoever doing all of this. David is correct - all of this is common sense, quick to do and the whole process takes less than 2 hours. I don't even mind repeating this process every six months because, as David points out, I have a new, shiny operating system with new features, additional applications, bug fixes and improvements.
What I have a problem with is trying to explain this whole, convoluted process to my father. Or rather, rescuing his system after he has failed to follow this process. Remotely.
Of course, my father has a few options available. If it ain't broke, don't fix it - if Mint Gloria works fine than stick rather than twist. Alternatively, he could use a distribution that automatically performs rolling upgrades so his software is always the latest and greatest.