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.
sigh.
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.
posted by Thomas Kyte at 2:38 PM
27 Comments:
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.
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...
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?
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.
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!
Actually i know more than one system where i can open your "Worst Practices" and say "gotcha!" on *EVERY* slide.
> 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
SQL = cockroaches
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...
Anonymous said: Lords over us have directed this to be "DB Independent"...
You have my sincere sympathies.
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.
Raj
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.
The answer is not 42 anymore, its xml
;)
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
xml, the way to take denormalization into uncharted territory!
...and the problem is solved via a XMLIndex. Great paradox XMLDB...it's like re-inventing a wheel, that's already been there for thirty years (I know I am sarcastic ;-)
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 (http://www.rittman.net/2005/10/16/sand-sybase-iq-and-column-based-databases/).
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),
Marco
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.
Talking about bad practices, what about this...
...
-- fetch out of sequence
PRAGMA EXCEPTION_INIT (wrong_order, -1002);
begin
..if batch_open
..then
....null;
..else
....raise wrong_order;
..end if;
...
Just saw this in real code!
@barto:
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...
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.
@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).
That tagline comment was shamelessly stolen from Ta bu shi da yu, which was in turn shamelessly stolen from somebody on Kuro5hin.
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
Flashlight
Led Flashlight
rc helicopter
helicopter
airplane
Playstation 2
Playstation 3
Nintendo DS
SONY PSP
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?
Thanks
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)
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 --
POST A COMMENT
<< Home