Is there a DBA in the development team?

You know a DBA didn’t participate in the design of the application or the database schema when:

  1. The primary key of the table is a numeric ID, generated by the application. Looks like the developers never heard of sequence objects.
  2. No foreign keys where two tables clearly reference each other.
  3. No indexes other than the numeric primary key, making it impossible to search for anything. Did I mention the table contained over 10 million rows?
  4. Instead of using partitions, the data is spread between about 100 tables and a view does “union all” so the application can query all the data.
  5. No bind variables are used, at all.

I’ve had all of these, and few more in today’s incident.

A user complained that he is seeing the wrong data. We noticed that he got the wrong data by searching the data table through an ID generated somewhere. We had to search a bit to find where the application got that ID because foreign keys were not used, but finally we found that the data table references an “additional data” table through an application generated id.

We discovered that few days ago the application started generating duplicate ids, and now when a user looks at the additional data he sometimes see the wrong data that is referenced by the same ID. We noticed that ids generated today duplicate ids generated 3 month ago, but we have no way of using this information because the date column is not indexed. In fact, nothing is indexed. I’m glad the “additional data” table is very rarely used.

The id should have been unique, but the data is spread through 100 tables in an attempt to mimic partitions – and the uniqueness cannot be enforced between tables.

As a temporary solution we ended up deleting the old data to clean the duplication. Not too easy with 100 tables, 10 million rows and no index in sight. I hope we bought enough time for the application developers to fix the issue.

Dear developers,

Oracle has many wonderful features. It has sequence numbers, it has constraints that can be used to enforce data integrity, it has partitions, it has indexes. Please make good use of those features.

Yours,
Production DBA

Advertisements

3 Comments on “Is there a DBA in the development team?”

  1. starprogrammer says:

    I bet that the “design decision” these features were not used was something like: “…to ensure compatibility with the all version of Oracle, Including-But-Not-Limited-To(tm), Oracle Berkeley DB”

    Actually, the “backward-compatibility-through-stone-wheel-invention” is a great anti-pattern.

  2. prodlife says:

    Hi Adi,

    I was going to write an article about Partitions, so I’m just writing a teaser here:

    Partitions are Oracle’s way of cutting a large table into smaller chunks. Resulting in improved performance and better manageability.

    Think of the DB as your bookshelf: If you just put books in random locations, you’ll have a long search each time you need a book. You can have an index that will tell you the location of a book by name, author, etc, but this is annoying to maintain. Much easier to bunch the SF together, technical books together, and porn under the mattress.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s