Category Archives: Database Design

Redundant NOT NULLs in DDL

I often see DDLs with NOT NULL definitions which is really redundant. When I see such DDLs I really question “do the writer really know what he or she is doing”. They might not hurt you application, but why set them if they are redundant? (If you know I reason please comment) Example: CREATE TABLE […]

One of the things you just need to know about Oracle …

A couple of years ago I did ask my developers to start using the varchar2(char) option when creating new tables. In Norway we have some characters in the alphabet (‘Æ’, ‘Ø’, ‘Å’) that uses more than 1 byte of storage (when the database characterset is set to AL32UTF8). Using the default – varchar2(byte) – could then decrease the number […]

Oracle 11g: New Locking behavior (modes) … an unexpected behavior. Or?

I just noticed some strange locking behavior in one of our production databases. It took me a while to reproduce this in test. I’ve based my example on Richard Foots blog “Oracle11g: New Locking Modes When Policing FK Constraints (A Wolf at the Door)”.

How PL/SQL can help improve your application design, and a lot more

As an Oracle resource taking part in development projects I’ve had the change to see how PL/SQL can actually increase the quality of both the application development and in application maintenance. In this article I’ll try to share this experience, and show how using PL/SQL will increase the quality of your application and your Oracle […]

11gR2: “Unlucky” combination of a new feature, a fix, application design and code

Sometimes a new feature (and/or a fix) could give some very unexpected results. In this article I’ll show how a mix between a 11g new feature (Adaptive Cursor Sharing), a fix (in 11.2, the obsolete threshold), application design (a separate schema for every customer), and code issues (not prefixing objects with schemaname, and bind datatype […]

So many DBAs; yet so many performance problems

There are many applications and databases around, performing and scaling just as supposed to. We very seldom hear about these project. Of course, this could be small applications with small loads; or, on the other hand, this could be well design application with well written code, and well maintained databases. Yet there are so many […]

Oracle foreign key ≠ referential integrity

I used to believe that implementing a foreign key in Oracle enforced referential integrity. Until today! Until reading this article by Tom Kyte, showing that this isn’t necessarily the truth. And I don’t think very many is aware of this. Yes – it does involve foreign keys allowing NULL values, but is’t that rather common?

Database Design Theory: “Normal Forms And All That Jazz”, C.J.Date

Seminar: Edinburgh, 7th and 8th of june Theme: Database Design And Relational Theory: Normal Forms And All That Jazz” w/Chris J. Date It takes a professional to question that you are NOT a database professional. There might be someone out there claiming that Chris Date is not a database professional, but I doubt it. After […]