Finding an Accidental Cross Join in an Oracle Query

Ever work on a query that joins multiple Oracle tables and one of them is cross joining on multiple rows resulting in multiple identical data rows in your output?  Ever sit wondering like me which which table is at fault? Found a neat way to figure that out without having to actually think.

We start with something like this:

SELECT 

      list_of_fields

FROM

      table_a  a,

      table_b  b,

      table_c  c,

      table_d  d

WHERE

      blah, blah, blah

Just add this to your list of fields:

     a.rowid "A", 

     b.rowid "B", 

     c.rowid "C", 

     d.rowid "D"  

(You don't have the "A", "B", "C" and "D" if you'd rather let all the columns be called "rowid" and count column positions--which is what I actually did.)

The output looks like gibberish, but on at least one of the columns the rowid will have at least one character different from the previous row within the same set of "identical" rows. That's the table at fault.

Best wishes!

The Failure of Large Business Applications

There was once a day when do-it-yourself software ruled. Companies hired scores of programmers to create COBOL applications that drove the business cycle. Then along came the software vendor who promised massive savings by splitting development costs across multiple businesses. The argument made sense so it wasn't long before in house applications were being replaced by purchased applications. The teams of in-house programmers were released as companies looked forward to large software savings.

But the costs continued to rise. . . and rise. . . and rise. . .

Those teams of in-house programmers were replaced by armies of contractors and off-shore developers who customized, tweaked and upgraded the newly purchased applications. To put it bluntly business applications have been an IT disaster. It is not hard to see why.

Fact: Software vendors make money on initial sales

Result: Initial sales are driven by the addition of new features.

Fact: Software vendors make lots of money on support

Result: Upgrades justify support

Fact: Supporting multiple versions costs money

Result: Only the most recent versions are supported

Overall Result: Businesses are forced into an upgrade cycle that only benefits the software vendor and costs businesses enormous amounts of money. While initial acquisition costs have dropped the total cost of ownership has spiraled out of control.

I believe there are solutions--which I will address in an upcoming post.

Is a Software Launch Like a Rocket Launch?

The minutes and hours leading up to the launch aren't very impressive. The rocket is slowly put into place. Equipment is checked and rechecked. Then all at once with a shuddering roar it happens.

Launching software systems can be very similar. First, good software requires detailed preparation. All possible scenarios must be examined , evaluated, implemented and tested. Secondly, regardless of the preparation when the rockets are ignited you never know exactly what's going to happen. Pieces shake loose, sometimes rockets even explode.

In the end the success of any kind of launch is based on the stability of the design and the preparation that went into it.

It's Not My Documentation

Almost every software developer knows it's good to document their software.

Almost every software developer doesn't.

Almost every software developer is smart.

Therefore, it is smart not to document software!

I say that partly in jest, but I really do think software developers are just not ideal at writing documentation. It's not that we're bad at writing. Most developers I've known are actually pretty good writers. The real reason software developers make such poor documenters is we generally don't know how to think like our audience. Writing documentation is like writing code--understanding the needs of the end user is critical. Our technical training simply doesn't prepare us to do well.

I've never tried this, but it seems that an ad-hoc team made up of a documentation consumer (at the keyboard) and a programmer (to provide information) would make a powerful combination.

The Critical Moment in Software Design

Imagine yourself in a meeting with a user (or group of users) discussing requirements for a new software feature. At some point--usually about 15 minutes into the discussion--the following phrase almost always comes up:

"But that doesn't happen very often."

It's often said with an awkward expression or laugh. This is the critical moment. Behind those non-committal words the user is really thinking--"These are details. This is my world. Does this geek care about my world? No, of course not." Of course behind our geeky glasses and awkward social graces we care a great deal about the user's world. The detail of the user's world is critical. Without detail there can be no accurate design. If the design is flawed then everything else will be broken too.

My response is typically like this:

"Often it's those things that don't happen very often that cause the most trouble. If you don't mind let's dig into this a bit more?"

I have never talked to anyone who was't thrilled to go into great detail once they felt that they had "permission" to get into the details.

The Missing Peice of Design

We've know for years that defects are easier (and cheaper) to fix during requirements and design than during coding and test. Yet that knowledge doesn't seem to reduce the number of requirements and design defects .

What's particularly weird is that all other factors being equal most "lets do it"(1) projects that I've observed turn out to have significantly fewer requirement and design defects than projects with heavy investments in design.(2) Until now I haven't understood why. In a Eureka moment late last week I finally realized why.

The key is that design does not come from reality. Design comes from a mental model of the world. Like this:

Requirements and design problems are almost always are rooted in the disconnect between the mental model and reality. It makes logical sense. Designers are almost always smart, dedicated and detailed. Once a piece of information has become part of a designer's mental model it is unlikely that it will be forgotten or corrupted between the model and the design. The more time a careful designer spends polishing and refining the design the less time they have to develop and verify their mental model. Thus the very thing--careful preparation--that is suppose to reduce risk actually increases it!

Of course modern software developers don't (I hope) follow the simplified model above. Instead we follow an iterative model. Here is the classic Boehm model from 1988.

The problem with this model is it doesn't reflect the disconnect between reality and design. Instead I propose the following:

If the mental model is close to reality projects will slide cleanly into stage 4. If not, there is a jarring bump as the design crashes into reality. If your design crashes, watch out. It will be tempting to slap a patch in place and move into the next iteration. Don't. Stop and re-calibrate. Scrap the entire previous iteration if you must. In the end it will be a lot cheaper than working around a flawed design for the life of the software.

----------------------------------

1) By "Let's Do It" I don't mean unplanned, I just mean less formal.

2) This may sound suspiciously like a argument for the Agile development process and perhaps it is. I've never worked in an Agile environment so I can't say.

Who Should Write the Docs?

User Documentation: It's tempting to hire an unemployed English major so that the writing is good. But the point of documentation is not good writing, it's useful writing. So skip on the writing expert and pick a system user. Someone who actually knows the pitfalls of the software in a real work environment will create a much more interesting and useful product. (Maybe hire the English major as an editor)

In Line Code Comments: I'm amazed at how many developers don't comment their code. I'm not talking about /* Start of loop */ -- oh duh. I could see that in the code. That kind of comment is stupid. What I am talking about is /* this logic is more complex, but it handles leap year where other alternatives don't */. In 5 years what would I need to know about this code? Document that. We need to demand of ourselves and our peers that undocumented code is unfinished code.

Compilation/Configuration Comments: For small shops with limited resources this is critical. Either the developer or whoever is in charge of deployment has to document this stuff. It's not fun. It simply must be a discipline.

Design Documents and Other Construction Artifacts: Nobody. No, I don't really mean that. But, as a general rule it seems that too high of a percentage of documentation time is spent generating documents that are useful for only a brief period during the early construction phases. These "big picture" documents are important. But there also needs to be detailed technical documentation describing the finished system. That step is so often left out.

Wiki, wiki, wiki: A document is never finished. Make it easy to update.