Is there a DBA in the development team?Posted: July 6, 2007
You know a DBA didn’t participate in the design of the application or the database schema when:
- The primary key of the table is a numeric ID, generated by the application. Looks like the developers never heard of sequence objects.
- No foreign keys where two tables clearly reference each other.
- 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?
- 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.
- 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.
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.