Thursday, November 09, 2006

Worst Practices...

At Oracle Open World, I gave a "worst practices" talk.  In this talk, I describe a series of "really bad things" people keeping doing and over and over again. 

This week, I've been in Canada doing some customer seminars.  I gave the talk in Montreal yesterday (November 9th) and today again in Quebec City.  Well received both times - and made all of the more fun by having some supporting "evidence".  Evidence that I do not make this stuff up.  Here is an email I received on the morning of the 9th (I like it when that happens, something in the news/email/whatever ties directly into what I'm doing that day).  This is reproduced with the senders consent (emphasis added by me):

Sorry about the unrequested email, but I couldn't resist...

I read your Worst Practices presentation the other day - Very nice, hit a bit close to home for comfort in many cases!

Then today I got an email from one of the contract "developers" our organization deals with, it describes a rewrite of a system that was rolled out a few years back.  It was a bit experimental and was always problematic - architectural mess - stuff flying around in files between ftp sites and windows shares and in and out of databases.  I (and my cohort DBA) kept asking "Why doesn't this just stay in a database and you query it from wherever". 

... BUT ....  It was developed shortly after one of our architect types had heard of XML, so XML had to be used, it wasn't really important what it was to be used for - it was just to be used... and so it was decreed, and it was made so, and it was good... well until the XXX "database" thing started crapping out every few days... but then some sys admin wrote a script to check and restart XXX every few minutes, and it was good again....  fast forward a few years....  decision is taken to rewrite and since our Oracle databases don't seem to crash every seventh minute, move the backend from XXX to Oracle....

Here is the "punch line" from the email describing the database aspects of the proposed system (slightly edited to remove reference to specific client):

"My current design for the Oracle-ized (Oracle 10g) version requires only a single Oracle table, which will have two columns: a pseudo key (simple varchar2) which will likely actually contain the path to a corresponding document in the WebDAV environment, and a document column of XMLType which will contain the xml for an individual "notice" within the [[snip]], plus an index on the pseudo key column."

Excellent - one table, with a key and XMLType column - the perfect system...  Is this a a cut and paste off slide 21 of your Worst Practices ppt or what???

If I could make this stuff up I could quit my job and work in stand-up.


Indeed, requires only a single Oracle table, which will have two columns - that is a cut and paste of that slide!

I do not make this stuff up, it is real.



Anonymous John Hurley said....

It was me suggesting one table from the audience at OOW when you asked how many tables any applications needs.

See ... this proves that I was right!

All those developers doing object oriented design certainly cannot be wrong.

Even a blind mouse finds a piece of cheese every once in a while.

Thu Nov 09, 03:39:00 PM EST  

Blogger Laurent Schneider said....

it is just incredible how many collegues went to internal XML workshop and come back with the solution of life, the universe and everything ;-)
I should visit one once...

Thu Nov 09, 04:04:00 PM EST  

Blogger Noons said....

looks like another fad is starting.
Is there no end to sheer, moronic, ignorant stupidity?
Like, the j2ee fad dodn't cause enough trouble, we now need to subject the IT world to yet another?

Thu Nov 09, 06:42:00 PM EST  

Blogger jimk said....

It happens because people become enamored with BSO (bright shiny objects) and don't understand the underlying fundamentals. As Brooks says "There are no silver bullets.". I make it a practice to reread "No Silver Bullets" and "The Mythical Man Month" every so often.

Thu Nov 09, 07:17:00 PM EST  

Blogger SeanMacGC said....

Being enamoured of BSOs is one thing, but why oh why are some so enamoured of DBMs (Dull Boring Misconceptions)? Yet again, I happen upon a strict and unbending directive to keep the application "DB Independent", and "No database code, i.e., Stored Procedures, Triggers, etc." Oh boy!

Thu Nov 09, 08:10:00 PM EST  

Blogger Q u a d r o said....

Actually i know more than one system where i can open your "Worst Practices" and say "gotcha!" on *EVERY* slide.

Thu Nov 09, 08:26:00 PM EST  

Anonymous Mark A. Williams said....

> Actually i know more than one system where i can open your "Worst Practices" and say "gotcha!" on *EVERY* slide.

Unfortunately those systems seem to be breeding like guppies in the data center fish bowl these days...

- Mark

Thu Nov 09, 08:55:00 PM EST  

Anonymous Toon said....

SQL = cockroaches

Fri Nov 10, 02:27:00 AM EST  

Anonymous Anonymous said....

SeanMacGC said... Yet again, I happen upon a strict and unbending directive to keep the ....

I know of a large multi-million pounds UK Govt. system upgrade from forms 2.5 to Java based multi-tier architecture. Lords over us have directed this to be "DB Independent", and "No database code, i.e., Stored Procedures, Triggers, etc. Java based connection pooling....Java based user authentication.... A third party XML based tool to simluate database link data transfers..."
Database is just a data dump...

Fri Nov 10, 05:35:00 AM EST  

Blogger SeanMacGC said....

Anonymous said: Lords over us have directed this to be "DB Independent"...

You have my sincere sympathies.

Fri Nov 10, 07:58:00 AM EST  

Blogger Rjamya said....

This reminds me of a recent tag line i read on slashdot ...

XML is like Violence, if it doesn't solve the problem, use some more.

Very true.

Fri Nov 10, 08:53:00 AM EST  

Blogger Robert Vollman said....

I remember my first foray into XML, back in 1999. We were bidding on a project and one of the questions was "does it use XML" so we answer "Yes, of course." And then I went and tried to figure out what the heck XML was and how to integrate it into the product.

I couldn't believe XML at first. It seemed like the absolutely least efficient and insecure way of getting data from one place to another.

Anyway, I modified the product by simply re-writing the functions that sent and received data. As it was going out, I converted our normal binary stuff into XML, and then back again in the receive function.

Fri Nov 10, 10:32:00 AM EST  

Anonymous dharma said....

The answer is not 42 anymore, its xml

Sat Nov 11, 01:46:00 PM EST  

Anonymous dharma said....

Oh and I forgot, Anybody seeing the changes Solaris 10 is undergoing with the xml fad, the simple text files have morphed into xml manifests
Shame on SUN

Sat Nov 11, 01:54:00 PM EST  

Blogger stephan said....

xml, the way to take denormalization into uncharted territory!

Mon Nov 13, 05:50:00 AM EST  

Anonymous Marco Gralike said....

...and the problem is solved via a XMLIndex. Great paradox's like re-inventing a wheel, that's already been there for thirty years (I know I am sarcastic ;-)

Mon Nov 13, 06:14:00 PM EST  

Anonymous Marco Gralike said....

Sorry, it just keeps itching…

The XML paradox isn’t just as easy solved as shown in slide 21. I even dare to argue that, from a XML point of view / and or even from a relational point of view, the solution presented could be a valid one. I had to skip your Worse Practices presentation, but seen from a theoretically viewpoint, XML addresses a solution regarding unstructured data exchange between different systems, that could be used for avoiding redundant data storage across multiple systems by a uniform accepted standard format. Just by avoiding the attempt, doesn’t mean that the problem shouldn’t be addressed.

IMHO Oracle didn’t come up, yet, with such a solution, that’s being accepted by most parties and fits in a Service Orientated Architecture. The hate-love relationship probably starts when we felt that we had to store XML in the database. In this respect, the dual column solution is not even so bad, as long as it is regarded as an “as is” atomic entity. The moment you want to query it or open it up for further manipulation the real fundamental trouble starts, because one tries to handle it as if it is a relational entity; which it isn’t. One can not apply a 3NV to it; there are no FK, PK relations in the sense as mend in a pure relational system. The chance is very real that by manipulating it, you will lose information or present information in a context which does not represent the original information. Trying to control it via XML Schema structures makes it maybe even worse, because it gives you the look and feel of a relational module, which, of course it isn’t… A document driven application, based on a single read-only XMLType column isn’t so bad, in that perspective, especially if its performance is really good via applying XMLIndex and binary XML storage methods.

Did you ever attend a Chris Date presentation? From his viewpoint a relational system should not deal with NULL values; ergo; he abhors SQL, which can cause a wrong presentation of the “stored” relational data, information. Among other efforts, he tries to re-module his own relational model to fit the current needs. With this reasoning in mind, does this mean that the Oracle database is not a good relational database?

From a Data Warehouse viewpoint it is sometimes wise to de-normalize data, even introduce data redundancy to address a performance need or re-build your database technology to accomplish a better supporting database structure like SAND or Sybase IQ (

Oversimplification of the problem, like slide 21, doesn’t prove (IMHO) your view. I really, really like the Oracle database, worked with it for several years, but I try to keep an open mind regarding different solutions for different problems. XML DB functionality will be part of the core database for a long time, and only time will tell if XML (DB) will be as successful as the relational database solution for addressing problems in data handling and / or data storage.

For now, just let me say: “it depends…” ;-)

regards (and with respect),


Tue Nov 14, 09:35:00 AM EST  

Anonymous Barto said....

You can go to the Oracle Magazine's page 39 (last issue), read the botton left text and see the light in XML and relational issue.

If the key is to store xml to save the effort converting the relational data into the presentation tier representation, I would propose a better solution: refactor the unique table to (id),(url),(text)
The text is just a html (you dont even need to put the xml into the presentation tier technology) and when you receive a request, find the corresponding text using the url as the find key. I think I would name my invention as 'web server'.

Sometimes, XML could be good. To send data over a queue, but i donde see the need to avoid structure when you can have it, and there is a lot of features which depend on this structure.

Thu Nov 16, 01:26:00 PM EST  

Anonymous Menno said....

Talking about bad practices, what about this...

-- fetch out of sequence
PRAGMA EXCEPTION_INIT (wrong_order, -1002);
..if batch_open
....raise wrong_order;
..end if;

Just saw this in real code!

Fri Nov 17, 07:48:00 AM EST  

Anonymous Marco Gralike said....


You can go to the Oracle Magazine's page 39 (last issue), read the botton left text and see the light in XML and relational issue.

I have found and read it (nov/dec issue), but as reasonable as it sounds, regarding "presentation", Tom has a point that XML doesn't solve some of the academic problems, the relational world already solved for some decades now...

Mon Nov 20, 05:14:00 AM EST  

Anonymous The Flying Spontinalli said....

Marco said:

"XML addresses a solution regarding unstructured data exchange between different systems, that could be used for avoiding redundant data storage across multiple systems by a uniform accepted standard format"

Data transfer has always required a uniform agreed form. XML is a bad choice even for this purpose because it is so inefficent, and so difficult to parse.

To transfer information between computers, best to use an efficient machine readable format.

You wouldn't listen to anyone who said storing MP3 files as XML was a good idea because all MP3 players could have XML parsers installed in them.

Enterprise data==same argument.

Mon Nov 20, 06:03:00 AM EST  

Anonymous Marco Gralike said....

@The Flying Spontinalli

I totally agree, but as i said (and you referred to it): "...uniform accepted standard format...". I did not say it was (and probably isn't) the answer to "...the universe and everything...". One could also argue, for instance, that for a lot of e commerce stuff EDI/EDIFACT is a far better solution (especially in handling transactions).

Toms post was regarding design / storage in a database. A relational purist would claim that because it is "unstructured" data; it could and should not be handled in this form in a relational database (which is all about "structured" data).

Mon Jan 08, 09:08:00 AM EST  

Blogger Chris S. said....

That tagline comment was shamelessly stolen from Ta bu shi da yu, which was in turn shamelessly stolen from somebody on Kuro5hin.

Wed Nov 21, 08:43:00 AM EST  

Anonymous Anonymous said....

Very good article. This article makes some interesting points. You made a good point, reading your article is a kind of enjoyment. Thank you.
Tactical Flashlights
r c helicopter
video game
Tactical Flashlight
Led Flashlight
rc helicopter
Playstation 2
Playstation 3
Nintendo DS

Mon Jun 23, 03:26:00 AM EDT  

Anonymous Anonymous said....

Hi Tom,

I look fowrad to reading your article 'Worst Practices', but I can not find it. It seems the link of 'Worst Practices' is broken. (top line of this page). Can you fix this link or let me where to get this article?


Tue Oct 28, 07:13:00 PM EDT  

Blogger Thomas Kyte said....

direct link to worst practices

You can find it by going to asktom -> files tab -> looking in the list of files (sort by date, was posted in October 2006)

Tue Oct 28, 07:34:00 PM EDT  

Anonymous Anonymous said....

Thanks Tom for visiting Atlanta and presenting the "Worst Practices". I downloaded your presentation from the link you published above. I was trying to run following sql from slide 8 :-

Select count(*)
From user_pw
Where uname = ‘tom’ or 1=1 – ‘
And pword ‘i_dont_know’ or 1=1 – ‘

But it didn't work. It worked after I added = after pword and replaced - with --

Thu Mar 26, 01:45:00 PM EDT  


<< Home