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 ?
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.
how I lost my Linux virginity
This week I am attending an excellent training course on Linux System Administration at Oracle's offices in Moorgate, near London. The course is given by Harald van Breederode, an excellent trainer who manages to combine deep technical, real-life expertise with a rare ability to explain complex things in simple terms with a sense of humour.
As is traditional, each attendee was asked to give a precis of his/her job role, star sign, experience with Oracle, most embarrassing professional moment, any exposure to Unix (Linux), favourite band and expectations for the course.
Although I have used various flavours of Unix since embarking on a degree in Computer Science at Warwick University in 1981 and then working for a variety of software houses, my first experience of Linux didn't come until December 1999.
At that time, I was working at Sequent and had ported a lot of GNU software utilities and tools to Dynix/ptx so I had an interest in GNU software. In the midst of the dot com boom, I had responded to a job posting on Usenet and subsequently been interviewed for a Oracle developer role at a small UK Internet company. The embryonic company had less than 10 employees and provided marketing reports, segmentation and campaign management based on click-stream analysis.
If successful, my first task would be to migrate 15 production Oracle databases from Oracle 8.0.5 on Windows NT to Oracle 8.1.6 on Linux. After the interview, I figured it might be prudent to actually familiarise myself with Linux and how to install and configure Oracle on this platform.
Back then, our house possessed a single desktop computer running Windows 95 so the first step was to obtain a copy of the Linux operating system. I sent off the princely sum of £3 (including postage and packing) to the wonderfully named Linux Emporium (which I am pleased to see survives to this day) and waited patiently for my CD to arrive in the post. Norma raised her eyebrows at a blank CD anonymously packaged in a plain brown envelope but still.
This method of Linux distribution seems a little quaint and old-fashioned when, nowadays, you can download and burn a Live CD in a matter of minutes but back than I had a dial-up 56K modem and downloading a full Linux distribution would probably have taken 3 weeks.
As I recall, I had sole use of the PC back then (Norma didn't use it for anything) so I didn't have to bother with a dual-boot system or any pesky backups so I simply inserted the CD, immediately made a pigs ear of partitioning the hard disk, inadvertently formatting it in the process and proceeded to install RedHat.
Given that I knew that my prospective employer had already selected as SuSE the preferred platform (they offered a bundled support package for Oracle and SuSE), the choice of RedHat seems a rather odd decision. I can only assume it was because I had already seen more FAQ's, HOWTO's and community support available for RedHat than SuSE. Although, knowing me, the fact that the RedHat CD was 50p cheaper than SuSE can not be ruled out.
Obviously the years, coupled with the drink and drugs, have taken their toll, so my memory is slightly hazy after 9 years but I do remember a tinge of excitement as white text on a black background announcing system messages detecting (or more likely failing to detect) various elements of hardware and peripherals rapidly scrolled past my eyes.
Eventually, I was surprised and delighted to see a desktop complete with a login box rendered on my screen. However, I was equally nonplussed but not wholly surprised when my mouse didn't work. Now although 'Tools are for fools', the Oracle 8i installer is normally run as a GUI (Java) application so, unfortunately, I had to resolve this issue.
Not for the first time, I was now completely stuck as I only had one computer. My impetuousness meant I couldn't simply boot back into Windows to research the problem so I did 'Altavista'ed at work, printing out a wealth of similar looking issues and resolutions and started troubleshooting.
Norma was a little suspicious of me being locked away for hours on end, messing around on the computer with my new shiny, blank CD but graciously ignored the alternating screams of anguish and ecstasy emanating from the bedroom.
I soon got comfortable with booting to a command line with no Windows ('init 3') and actually managed to configure a device driver for my mouse and I believe I may have even recompiled the kernel. Not because I needed to but well, because I could.
When I eventually got movement out of the rodent and was able to log in, it was incredibly satisfying.
After that, installing Oracle was fairly straightforward as I had already used Oracle on Solaris. However, I recall running Oracle in my environment was a rather different matter as I was perilously close to the absolute minimum specification (64MB of of physical memory) required for Oracle 8i.
All of this work paid dividends as I was summoned back for a second interview and I was able to talk a little more knowledgeably about the potential issues and pitfalls of a Windows to Linux Oracle migration.
Needless to say, this was rather a verbose answer for the course so I just said 'I once blagged a job as an Oracle DBA managing Oracle 8i on a handful of self-built servers running SuSE.'
onwards and upwards
After over five varied and enjoyable years working for Siebel (and then Oracle) in Expert Services, I am changing jobs. However, I will continue to work for Oracle as part of the group responsible for 'Social CRM'.
Although this was a internal transfer, my interview process was quite unusual. Instead of being forced to massage my CV into Microsoft Word format and send an email attachment to a faceless recruitment agency, I was able to publish my CV using Google Docs and the whole interview process was conducted by telephone.
A colleague had already thoughtfully pointed my prospective manager to a blog entry that was loosely relevant and he also was keen to review more technical oriented content on my Siebel related blog.
As an aside, you can only imagine the feelings and thoughts that flooded into my brain when my colleague uttered the immortal words: 'Hey Andy, I've given your <prospective new manager> a pointer to a couple of articles on your blog.'
The role will mean a few changes for me:
- I will now revert to having difficulty explaining to friends and relatives what I actually do.
- I will probably spend less time in airport lounges and more time with my family. Norma has already expressed grave reservations about this element of my career change.
- My manager will be in a different continent and timezone.
- I will be contributing to a corporate blog for the first time which will necessitate a slight shift in subject matter, terminology and slightly less of my dry, cynical, off the wall, British sense of humour although I do intend to maintain this personal blog.
I am currently tidying up a few loose ends in Expert Services and embarking on a emotional, sell-out, European farewell tour to all my favourite Siebel customers so I won't actually be starting my new role until mid-October but it will be an exciting change for me and I'm really looking forward to it.