I saw a funny one today. I’ll paraphrase:
select * from table_x where upper(acct_number) = '876876' or upper(acct_number) = '826531';
1. Obviously turns off any indexes on acct_number (unless they had a function based index).
2. Looks like they are probably storing numeric data in a character data type (or implicitly converting a number to character string).
3. Not using bind variables so they are not helping themselves from a parsing standpoint.
4. Finally, they’re making darn sure they take care of any mixed case numbers!
(by the way, those lower case numbers cause me problems all the time)
As information technology professionals, we constantly complain about mismanaged projects in which we have the misfortune to be involved. Frequently, someone with more power than knowledge – usually in management and under the sway of a persuasive vendor sales team – has come up with a systems design most politely described as novel. Because of our experience and understanding of the technology, we can clearly see the plan will fail. Heedlessly, despite our loud protests, the project plods onward toward oblivion, consuming money, time and our human spirit along the way.
We feel bitter and exasperated at being ignored. We are shocked at the audacity of spending a corporation’s money when you don’t know what you are doing. Our most common reaction is to escalate, complain, and engage in a battle of wills with the author of the flawed plan.
We must change our thinking. To engage in resistance to doomed projects is both futile and self-destructive. By releasing our attachment to the idea that projects must be done right, we begin to see the virtuous effects of projects being done wrong.
Of course it is our duty to register our objections to a flawed plan to those in charge in email, using clear, technical and unambiguous terms. Beyond that, however, our emotional involvement should end. In the event our customer or employer deploys a badly designed system, it will surely require many skilled, competent troubleshooters to fix the system. That’s us! Incompetent systems design results in strong demand for competent technical people.
But the benefits to the I.T. economy are not restricted to the aftermath. During implementation of a failed system, a company pays vendors and employees to build it, even though they lack the skills or knowledge to realize their system is doomed. Without doomed projects, these people would not even be part of an I.T. economy. It is like a kind employment stimulus based on stupidity.
So don’t feel hate and loathing if you are in the midst of a doomed project, and nobody will listen to you. Release your attachment to the need for others to do things right, and wait for failure. When it comes, do your best to help.
I’m not saying I think this is how things should be. I’m just saying it works paradoxically to the benefit of good technical contributors. It certainly is not something to get bent out of shape about.
Wow, it will be a full house during Miracle OpenWorld 2010, as Moans announced during this video, maybe there won't be enough beds, maybe there won't be enough food, but Moans at least promised us, the 208+ attendees, the new Miracle Brew, anyway, have a look...
Xtrace is a graphical tool that can navigate Oracle trace files, manipulate them, and optionally get them back as a text file. It actually makes (much) more, but in this first post we are going to focus on its basic browsing capabilities.
Let’s see the tool in action on the trace file produced by this simple PL/SQL block:
begin for r in (select * from t) loop null; end loop; end;
The resulting trace file is
This week I’ve seen an issue with a CONNECT BY query: for some reason Oracle 10.2.0.4 decided to build a weird plan (the query is weird too, but that’s not my point here ). An explanation of why that happened looks interesting, so here it is. Set up: drop table t2 cascade constraints purge; drop [...]
My apologies for my extended absence. Well, I ran into something interesting today, and, I thought it would be appropriate for a blog post, and apparently, I got inspired. No startling revelation here, this is just a bit of a cautionary tale about the unintended consequences of using a new feature. A quick search of the Oracle Documentation confirms that the
skip locked directive of the
select for update statement was introduced in Oracle 11g, version 11.1. But, before we dive into that, let’s review the functionality of
select for update through versions of Oracle preceding 11g.
Select for update
Thanks to Marco mentioning it on Twitter, I downloaded and installed 11gR2 for Windows from OTN this evening. The reason I had to switch to OEL on VMWare was because of the delay on the 11gR2 Windows release, but I'll give it a try and see which works best for me. Installation was certainly a breeze.
Here’s a piece of code I found recently running every half hour on a client site:
SQL_ID = 2trtpvb5jtr53
TO_CHAR(current_timestamp AT TIME ZONE :"SYS_B_0", :"SYS_B_1") AS curr_timestamp,
COUNT(username) AS failed_count
For the last couple of months I have been quite busy delivering Oracle11g New Features courses and one of the topics covered is table compression. To introduce this topic I ask my students in which version the first compression feature was introduced. Strangely enough almost no one seems to know the correct answer, which is [...]