Skip navigation

Oracle technologists, database architects, database administrators, and senior database application developers are all very curious people by nature – even more curious than most people. So naturally database oriented issues evoke debates similar to “tastes great, less filling” from the famous beer commercial. You might be surprised by the scope and passion of such database debates. One area getting lots of discussion of recent is database virtualization – where many seem opposed just for the sake of opposition. I‘ve written a few articles why fighting against virtualizing databases is a losing proposition.

 

 

I’ve also written several articles on how to approach successful testing of database virtualization testing on notebooks and preparation for actual deployments.

 

 

Yet the skeptics remain. So be it. Hence I’m going to focus this article on comparing some common virtualization solutions Oracle technologists can use to test drive virtualized databases on their workstations – namely VMware Workstation ($189) vs. Oracle Virtual Box (free for personal use, $50 for commercial use). For testing these tools are great, for real deployments you would of course choose their industrial strength offerings: VMware vSphere and Oracle VM, respectively. But it’s my hopes that comparing the workstation versions, plus doing so on Windows vs. Linux as the host, will glean some useful information for peoples’ curiosity regarding virtualization and databases.

For testing I’ll be using Quest’s Benchmark Factory version 6.7 (which I’ll be referring to as BMF for short) to run the industry standard TPC-C benchmark. The databases will be fairly stock installs of Oracle 11g R2 with a few initialization parameter modifications, but otherwise pretty much the default or “out of the box”. The Linux and Windows hosts and VM’s too are pretty much stock, with just a file system modification to improve database IO performance:

 

  • For Windows:
    •   HKEY_LOCAL_MACHINE\System\CurrentControlSet\ Control\FileSystem\NtfsDisableLastAccessUpdate = 1
  • For Linux:
    • Edit /etc/fstab file and add the “NOATIME” attribute
    • Example: /dev/sda2        / ext3 defaults,noatime    1 1

 

The following hardware (two identical boxes) and software was used for all reported testing:

 

  • Windows Host
    • AMD Phenom II X4 955, 3.2 GHz CPU, Quad Core
    • 16 GB RAM DDR-3 RAM
    • RAID-0 (striped), Dual 7200 RPM SATA-II Disks
    • Windows Server 2003 Enterprise R2 64-bit with SP2
    • VMware Workstation for Windows 8.02 64-bit
    • Oracle Virtual Box 4.12 for Windows 64-bit
  • Linux Host
    • AMD Phenom II X4 955, 3.2 GHz CPU, Quad Core
    • 16 GB RAM DDR-3 RAM
    • RAID-0 (striped), Dual 7200 RPM SATA-II Disks
    • Ubuntu Desktop Linux 10.04 (lucid) 64-bit
    • VMware Workstation for Linux 8.02 64-bit
    • Oracle Virtual Box 4.12 for Linux 64-bit

 

The following virtual machines were tested on both hosts and under each virtualization platform:

 

  • Windows VM
    • 2 CPU with 2 threads per CPU = 4 CPU’s
    • 8 GB RAM
    • Windows Server 2003 Enterprise R2 64-bit with SP2
    • Oracle 11g R2 (11.2.0.2) 64-bit
  • Linux VM
    • 2 CPU with 2 threads per CPU = 4 CPU’s
    • 8 GB RAM
    • Redhat Enterprise Linux 5.4 64-bit
    • Oracle 11g R2 (11.2.0.2) 64-bit

 

Due to technical difficulties such as not being able to open the Windows VM’s .VMDK file under Virtual Box due to Windows issues with storage driver properties settings (i.e. SCSI vs. IDE) and not being able to configure Linux for running two virtual machine platforms both requiring hardware virtualization support at the same time – the matrix of test scenarios was reduced to as shown in the following table:

 

 

VM OS

Virtualization Platform

VMware

Virtual Box

 

Host = Windows

Linux

Run-1

Run-3

Windows

Run-2

X

 

Host = Linux

Linux

Run-4

X

Windows

Run-5

X

 

Figure 1 below displays the BMF TPC-C benchmark results for transactions per second, often simply abbreviated and referred to as TPS. Unfortunately although commonly favored, TPS is a generally misunderstood and largely misinterpreted metric.

 

bbb1.png

Figure 1: Transactions / Second

 

Figure 1 clearly demonstrates two very key findings. First, that the various host operating systems, virtualization platforms, and VM operating systems combinations do not make a substantial difference. In other words the transactions per second results are essentially the same across all the various combinations – meaning that no one solution is better or worse than the others. The second and more important result is that the transaction per second or TPS rate rises with the concurrent user load. So at 120 concurrent users the score of 6.3 TPS is triple that at 40 concurrent users of 2.1 TPS. Of course that’s the expected result – i.e. the more concurrent users the higher the TPS rate until some threshold is reached breaking that trend. Since Figure 1 results have not yet reached that threshold, we could deduce that we can keep growing the concurrent user load even higher until the inflection point is finally reached. That however would be where we’d make the common and devastating mistake – and why TPS by itself is so dangerous.

 

Figure 2 below displays the BMF TPC-C benchmark results for average response time (in milliseconds), the one metric that all business end-users know quite well and often judge acceptable database application performance by – often even stating service level agreements or SLA’s in terms of average response time maximums. Unfortunately although the best understood and simplest metric to rate, average response time is quite often overlooked by technical people doing benchmarks in lieu of TPS and corresponding calculable physical IO rates such as IOPS. In fact these same technical people will often lower or remove the benchmark’s “keying & thinking” time in order to inflate those numbers. In essence they are more interested in engine’s tachometer (i.e. RPM’s) than the speedometer (i.e. MPH).

 

bbb2.png

Figure 2: Average Response Time (in milliseconds)

 

Figure 2 also clearly demonstrates two very key findings – so key in fact that they are the crux of the actual knowable results from this benchmark testing. First, the various host operating systems, virtualization platforms, and VM operating systems do vary in terms of performance. The various combinations are not in fact all equal. Some choices such as Run-4 (Linux host, VMware and Linux guest) performs markedly worse across the board. While other choices such as Run-2 (Windows host, VMware and Windows guest) perform somewhat inconsistently – i.e. worse at lower concurrent user loads, but gradually improving as load increases. However second and the more critical finding is that assuming a common two second response time SLA requirement, we have quite a ways to increase the user load before we’re likely to cross that threshold. Note that TPS give us no clue as to where we are in relation to any limits, just that the numbers are getting better until they don’t. Average response time clearly indicates to us where we are in relation to that limit. In fact taking Run-1 (Windows host, VMware and Linux guest) and increasing the concurrent user load until that 2 second limit on average response time was reached took over 1,000 concurrent users. So MPH do in fact tell you when you’ll arrive at your destination more so than RPM’s.

 

In closing let me clearly state that I do not intend these results to suggest nor pick any winners nor losers. Merely that these benchmark results show clear evidence for me personally to make my own conclusions in some very specific testing scenarios. There are no universal truths indicated here regarding those choices. Hence I am not trying to suggest which host OS, virtualization platform or guest OS is best. Rather to me they are all just fine and hence they are all on my tool belt. What I do suggest is that virtualization is here to stay, it works and works well, so quit swimming against the current and embrace it – and when doing so proper benchmarks and their correct results interpretations can lead to very successful virtualized database deployments.

In the good old days most DBA’s could walk into their computing center and point to their database server and its disks. Furthermore, many DBA’s could also point to specific disks and state which database objects they housed. In fact database object placement and separation was a key tuning consideration back in the day. Oh my how times have changed.

 

At first all database servers utilized direct attached storage devices (i.e. DASD). In many cases the database server had limits as to the number of DASD it could support. Thus a big database often required a big server. So there was a direct one-to-one correlation between database size and server size.

 

Then along came Storage Area Networks (i.e. SAN). Think of a SAN as a storage server designed to house many disks and connected back to the database server via expensive, high speed fiber channels. Smaller databases tended to remain on DASD, while larger ones often got a dedicated SAN. Then as SAN technology improved, small to medium databases started sharing a SAN.

 

Not too long after the SAN along came the Network Attached Storage (i.e. NAS). Think of a NAS as a storage server designed to house many disks and connected back to the database server via inexpensive and common Ethernet. At first Ethernet speeds of 100Mbit restricted NAS usage to primarily smaller and less critical databases. However as Ethernet speeds reached 1Gbit and now 10Gbit, NAS has become a viable choice for most databases storage needs.

 

Finally both SAN and NAS evolved, adding some new and interesting wrinkles such as Fiber Channel over Ethernet (i.e. FCoE) and Internet Small Computer System Interface (i.e. iSCSI). But essentially SAN and NAS device architectures have remained the same. Just the connection methods were modernized. So the basic architectural diagrams did not really change.

 

In either of these storage scenarios (i.e. SAN and NAS), the DBA generally lost all touch with database object to disk mappings. At first when the SAN or NAS device was generally for but one database, some DBA’s may have performed or assisted with the Logical Unit (i.e. LUN) design. But as these storage devices became shared, often the DBA was simply assigned a black box of space for the DBA specified size requirements.

 

So let’s assume that the DBA requested 80GB of disk space for the database and the storage administrator created four 20GB LUN’s for that request. There are many reasons why the storage administrator might have created for LUN’s rather than one. It might be that the LUN’s are on different SAN and/or NAs devices. It’s also quite possible that storage administrator simply wanted to provide more one LUN so that the DBA could perform some logical separation of database objects.

 

aaa1.png

Figure 1: Database Storage Design

 

Thus the DBA might design the database storage as shown above in Figure 1. Basically the DBA thinks he has four disk drives (i.e. the four LUN’s) and lays things out accordingly. But he doesn’t know where the spindles are or how may he may be striped across due to this basic storage abstraction. And as Figure 1 shows, the DBA may introduce further abstractions by using a Logical Vole Manager (i.e. LVM) or Oracle’s Automated Storage manager (i.e. ASM). Furthermore the use of database logical containers and constructs such as table spaces and partitions adds yet additional level of abstractions. But it now gets even worse as shown below in Figure 2.

 

aaa2.png

Figure 2: Virtualized Storage Architecture

 

With storage virtualization, the storage administrator can now manage space across storage devices in a manner much like a LVM. Thus the physical LUN’s are aggregated into a centralized and shared “Storage Pool”. Now he can create and assign logical LUN’s for the DBA’s need. Of course that means yet another level of abstraction and thus removal from knowing where your disks are.

 

Thus we need to realize and acknowledge two key points. First, we essentially virtualized (i.e. abstracted) storage a very long time ago with the concept of the LUN. And second, the virtualization craze is simply adding yet another layer of abstraction or removal from your disks. You now have logical LUN’s.

 

So forget about ever really knowing where your disks are and how your database objects are laid out across them. You’ll need a time machine if you really must know. I’d say just forget it – there are bigger fish to fry.

Bert Scalzo

Flashback to the Rescue

Posted by Bert Scalzo Dec 2, 2016

Face it, we’ve all been there – that moment when we’ve done something a wee bit too fast and then realize we probably shouldn’t have done it. Then we often generally say something like “stupid database” or “stupid TOAD’ or “stupid whatever. But there are ways to recover without having to go hat in hand to the DBA and beg forgiveness and obtain a recovery. More recent versions of Oracle offer several wonderful “Flashback” options that can often save the day. I’ve included a summary of all those options with an excerpt from my book “Advanced Oracle Utilities: The Definitive Reference”.

 

DBMS_FLASHBACK

 

At the beginning of this chapter when discussing various use case scenarios for the data pump export and import were mentioned. Namely – that a DBA might need the ability to restore collections of tables to some pristine state on a regular interval. In the prior section we just examined doing restructures where a backup or before image might be handy in case of problems mid-restructuring. The truth is that there are many cases where the DBA would like the ability to restore a logical portion of the database back in time or to a prior SCN. But the problem is that physical backups and restores are very complex – and operate more at the physical database level. And few applications are generally worthy of their own backup and recovery strategy using the physical tools to build logical or application based restore points.

 

But a long time ago Oracle introduced a great concept in the SQL and PL/SQL languages called the SAVEPOINT. This was the ability to issue a database state bookmark within you application code such that you could rollback to an application based logical point in time. This was a useful technique, but never really saw extensive usage. But it was a good concept nonetheless – if it only it would have extended to database objects and/or even the database level itself. Well now it does – we have Oracle’s flashback technology.

 

Oracle flashback technology essentially lets you create SAVEPOINT like bookmarks to restore to for either objects or the entire database. In some respects it’s a great short term point-in-time recover technique – rather than going to a full blown backup and restore. Plus its usage has been made so integrated, seamless and easy – that it’s sure to see heavy usage as time goes on. It’s truly a key new must-have tool for the DBA’s tool belt.

 

There are six flashback technologies (in chronological order of their appearance) whose topics we’ll examine in more detail:

 

  • Flashback Queries (9i)
  • Flashback Drop (10g)
  • Flashback Tables
  • Flashback Database
  • Flashback Transaction (11g)
  • Flashback Archives (i.e. Oracle Total Recall)

 

Furthermore, you’ll see that unlike other features discussed in this chapter – you’ll want to learn how to utilize these various flashback technology capabilities via OEM, SQL commands and the PL/SQL API.

 

Note – Part of the reason for covering all the flashback technologies here, including a recap of older ones, is to hopefully lead the reader along the historical path of flashback technology development – and thus to perceive that each step was built on the foundations of those prior.

 

Flashback Queries

 

Oracle 9i introduced the concept of the flashback query. I like to call this the “Back to the Future” or time machine type query – where Oracle lets us make some queries in the present as if from a database state in the not too distant past.

 

The black magic that makes this possible are UNDO tablespaces and automatic UNDO management – and that Oracle now treats those UNDO blocks as first rate data based upon the UNDO_RETENTION parameter. Using these, Oracle does its best to retain UNDO data (not guaranteed). You can even force that availability via the UNDO tablespace RETENTION GUARANTEE option.

 

In its simplest form, we merely add an AS OF clause to the SELECT statement to request the current execution be performed as if it were run at some prior specified time or system change number (i.e. thus looking backwards in time).

 

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

 

GET_SYSTEM_CHANGE_NUMBER

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

                  353026

 

SQL> update movies.customer set zip='99999';

 

62 rows updated.

 

SQL> commit;

 

Commit complete.

 

SQL> select firstname, lastname, zip from movies.customer as of scn 353026 where rownum < 5;

 

FIRSTNAME            LASTNAME                       ZIP

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

Flozell              Adams                          75063

Troy                 Aikman                         75063

Larry                Allen                          75063

Eric                 Bjornson                       75063

 

We also can enable the entire Oracle session to enter a “time tunnel” or “time warp” so that we don’t have to add anything at all to the SELECT command to see such historical data. In that case we simply enable and disable the flashback effect as shown here via the PL/SQL packages found in DBMS_FLASHBACK.

 

SQL> select firstname, lastname, zip from movies.customer where rownum < 5;

 

FIRSTNAME            LASTNAME                       ZIP

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

Flozell              Adams                          99999

Troy                 Aikman                         99999

Larry                Allen                          99999

Eric                 Bjornson                       99999

 

SQL>

SQL> execute DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(353026)

 

PL/SQL procedure successfully completed.

 

SQL>

SQL> select firstname, lastname, zip from movies.customer where rownum < 5;

 

FIRSTNAME            LASTNAME                       ZIP

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

Flozell              Adams                          75063

Troy                 Aikman                         75063

Larry                Allen                          75063

Eric                 Bjornson                       75063

 

SQL> execute DBMS_FLASHBACK.DISABLE

 

PL/SQL procedure successfully completed.

 

Flashback Drop

 

Oracle 10g introduced the flashback drop concept – which is built off the new recycle bin in the database (think of it as much like the Windows recycle bin). Now when you drop an object, it’s moved first to the recycle bin (assuming that the recycle bin has been enabled via the RECYCLEBIN=ON init.ora parameter). The recycle bin simply retains the dropped objects under a new unique name.

 

SQL> drop table junk;

 

Table dropped.

 

SQL> select object_name, original_name, type from recyclebin;

 

OBJECT_NAME                    ORIGINAL_NAME                    TYPE

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

BIN$oP2i2G1STvita2AHhmFdVw==$0 JUNK                             TABLE

 

Then when we want to un-drop a table, the process is very easy – we simply use the FLASHBACK TABLE command to restore the table as it was before the drop. When the table flashback is invoked, not only are the table and its data brought back – but so are any dependent objects and grants. It’s that simple.

 

SQL> select * from junk;

 

        C1         C2

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

1          2

 

SQL> drop table junk;

 

Table dropped.

 

SQL> select * from junk;

select * from junk

              *

ERROR at line 1:

ORA-00942: table or view does not exist

 

SQL> flashback table junk to before drop;

 

Flashback complete.

 

SQL> select * from junk;

 

        C1         C2

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

         1          2

 

For those who prefer a graphical interface, OEM has a rather easy screen for doing object level complete recoveries (i.e. un-drop).

 

xyz1.png

Figure 4: OEM Object Level Recovery Screen

 

Prior to the flashback drop capability, the best method for doing object level recoveries was the logical backup method discussed earlier in this chapter (i.e. export/import). But handling referential integrity and dependencies was a problematic manual effort requiring significant investment to get 100% right.

 

Flashback Tables

 

The next logical step in the flashback progression is to do more than to undo a simple table drop – but rather to permit the table to return to its prior state as if making a flashback query permanent. Once again we have yet another key new requirement for this latest flashback feature to work – tables must have row movement enabled (feature that permits Oracle to change the ROWID of a row, otherwise they are usually immutable). Now we can use the prior section’s flashback syntax to return a table to some prior specified time or system change number – as shown here.

 

SQL> create table junk (c1 int, c2 int) enable row movement;

 

Table created.

 

SQL> insert into junk values (1,2);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

 

GET_SYSTEM_CHANGE_NUMBER

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

                  362096

 

SQL> insert into junk values (3,4);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> flashback table junk to scn 362096;

 

Flashback complete.

 

SQL> select * from junk;

 

        C1         C2

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

         1          2

 

Once again for or those who prefer a graphical interface, OEM has a rather easy screen for doing object level point in time table recoveries (i.e. flashbacks).

 

xyz2.png

Figure 5: OEM Table Screen

 

xyz3.png

Figure 6: OEM Table Flashback Screen

 

Flashback Database

 

Continuing with our flashback technology progression, the next big step is to permit one to flashback an entire database. In essence we now want to perform a point in time recovery at the database level – and all very simply and without actually doing anything in RMAN. Once again we have yet another key new requirement for this latest flashback feature to work – the database must be running in ARCHIVELOG mode. Plus, we can only flashback to whatever online redo logs are immediately available via disk – any further back in time requires a traditional point in time recovery via RMAN. But for many cases even that short time span may suffice as the first-level basic point-in-time recovery strategy. The next example may look quite a bit like the last for the flashback table, we now simply have to take the database to a mounted status and issue the FLASHBACK DATABSE command as shown. All that’s changed is the scope of the operation.

 

SQL> create table junk (c1 int, c2 int) enable row movement;

 

Table created.

 

SQL> insert into junk values (1,2);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

 

GET_SYSTEM_CHANGE_NUMBER

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

                  365991

 

SQL> insert into junk values (3,4);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> connect sys/mgr as sysdba

 

Connected.

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  627732480 bytes

Fixed Size                  1334996 bytes

Variable Size             398459180 bytes

Database Buffers          222298112 bytes

Redo Buffers                5640192 bytes

Database mounted.

SQL> flashback database to scn 365991;

 

Flashback complete.

 

SQL> alter database open resetlogs;

 

Database altered.

 

SQL> connect bert/bert

 

Connected.

 

SQL> select * from junk;

 

        C1         C2

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

         1          2

 

We’ve now wrapped up the flashback technology historical progression as of Oracle 10g release 2 – so now it’s onto even bigger and better flashback capabilities with Oracle 11g. However whereas the progression and granularities up until this point were probably fairly obvious, we’re now moving into less obvious but critically useful flashback techniques – hopefully with a decent background and understanding now to appreciate just how we got here.

 

Flashback Transaction

 

The DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure rolls back a transaction and all its dependent transactions. As with all the other flashback technologies discussed so far, the transaction back out operation uses UNDO to create and execute the compensating or opposite transactions that return the affected data to its original state. So in some respects the granularity of this flashback operation is somewhere between our last two cases: table and database. We’re now flashing back a subset of the database that represents some logical collection of tables and queries. It essentially implements the prior mentioned SAVEPOINT concept in the database now rather than the application code. In fact, flashback transactions mostly eliminate the need for the next section on redo log file mining – as it’s now transparently and more easily done as shown here.

 

First, we query the FLASHBACK_TRANSACTION_QUERY view to see what transactions exist for whatever objects and/or users we think may have done something that need undone. This view can return a lot of information in even a mildly busy database, so filtering is highly recommended. Here I’ve asked to see what transactions have occurred in the past day by logon user BERT and on tables owned by BERT. Note that this view offers us the UNDO SQL code.

 

SQL> select xid, start_scn, operation, table_name, undo_sql from flashback_transaction_query where start_timestamp>=sysdate-1 and username='BERT' and table_owner='BERT';

XID               START_SCN OPERATION    TABLE_NAME

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

UNDO_SQL

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

0200030052030000     475697 DELETE       JUNK

insert into "BERT"."JUNK"("C1","C2") values ('5','6');

 

0200030052030000     475697 DELETE       JUNK

insert into "BERT"."JUNK"("C1","C2") values ('3','4');

 

0200030052030000     475697 INSERT       JUNK

delete from "BERT"."JUNK" where ROWID = 'AAAD94AAAAAAChOAAD';

 

0200030052030000     475697 INSERT       JUNK

delete from "BERT"."JUNK" where ROWID = 'AAAD94AAAAAAChOAAC';

 

0200030052030000     475697 INSERT       JUNK

delete from "BERT"."JUNK" where ROWID = 'AAAD94AAAAAAChOAAB';

 

0200030052030000     475697 INSERT       JUNK

delete from "BERT"."JUNK" where ROWID = 'AAAD94AAAAAAChOAAA';

 

If I now want to simply undo the two delete commands (whose undo action to re-insert the data that was deleted), here’s the PL/SQL code for doing that.

 

SQL> select * from bert.junk;

 

        C1         C2

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

         1          2

         7          8

 

SQL> declare

   trans_arr XID_ARRAY;

begin

   trans_arr := xid_array('0200030052030000','0200030052030000');

   dbms_flashback.transaction_backout (

        numtxns         => 1,

        xids            => trans_arr,

        options         => dbms_flashback.cascade

   );

end;

/

 

SQL> select * from bert.junk;

 

        C1         C2

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

         1 2

         3          4

         5          6

         7          8

 

Once again for or those who prefer a graphical interface, OEM has a rather easy screen for doing object level point in time transaction recoveries (i.e. flashbacks).

 

xyz4.png

Figure 7: OEM Table Screen

 

xyz5.png

Figure 8: OEM Transaction Flashback Screen

 

Flashback Archives

 

The final piece of the puzzle in the flashback progression is the new Oracle 11g flashback archive. You define an area that provides the ability to automatically track and archive transactional data changes to specified database objects. These flashback archives become user named and somewhat then managed persistence of UNDO at the specified object level. So when you need to do a SELCT with an AS OF, you can rely on the object being in your chosen container for the specified duration and competing for space only with the objects you so choose. Thus it’s merely a named are to support all the prior flashback features we’ve just examined.

 

SQL> create tablespace flash_archive

datafile 'c:\oracle\oradata\ordb1\flash_archive.dbf' size 50M;

 

 

Tablespace created

 

SQL> create flashback archive default flash_archive tablespace flash_archive retention 30 day;

 

Flashback archive created.

 

SQL> create table bert.junk (c1 int, c2 int) flashback archive flash_archive;

 

Table created.

Probably one of the most often discussed and hotly debated topics in both data modeling and database design is that of normalization. Many database designers, architects, DBA’s and senior developers have differing positions and/or beliefs on the topic. However quite unfortunately, they often are not communicating with optimal effectiveness due to some fundamental differences in terminology and understanding. The simple solution is often to take five minutes to review normalization basics – and thus get everyone on the same page. That’s what this  blog entry strives to do.

 

However before you proceed, always ask yourself what kind of database and application you’re building – because many important design issues vary widely depending on the target system’s nature shown in the chart below.

 

 

OLTP

ODS

OLAP

DM/DW

Business Focus

Operational

Operational Tactical

Tactical

Tactical

Strategic

End User Tools

Client Server

Web

Client Server

Web

Client Server

Client Server

Web

DB Technology

Relational

Relational

Cubic

Relational

Trans Count

Large

Medium

Small

Small

Trans Size

Small

Medium

Medium

Large

Trans Time

Short

Medium

Long

Long

Size in Gigs

10 – 200

50 – 400

50 – 400

400 - 4000

Normalization

3NF

3NF

N/A

0NF

Data Modeling

Traditional ER

Traditional ER

N/A

Dimensional

 

For the purposes of this discussion, let’s assume that you’re working on traditional OLTP database and application. Thus you’re hoping to achieve third normal form. Let’s review what that means – and specifically, without using fancy mathematical definitions and/or terms that only PhD’s might understand. Let’s make normalization both easy and fun J

 

Remember the old “Perry Mason” television show? (maybe I should have said Matlock so as not to date myself) When a witness takes the stand – the clerk asks “Do you swear to tell the truth, the whole truth, and nothing but the truth – so help you God?” Well, we can utilize that simple little jingle to both define and remember normalization. Our catch phrase will be “… the key, the whole key, and nothing but the key – so help me Codd”. I’ll assume everyone knows who Dr. Codd was – the father of modern relational theory.

 

First Normal Form

 

There are four fundamental requirements for an entity or table table to meet first normal form – all of which must hold true:

 

  • The entity or table must have a defined and active primary key
  • The primary key is the minimal set of columns that uniquely identify a record
  • Each attribute or column in the table contains a single value (i.e. atomicity)
  • Eliminate repeating groups (i.e. no arrays)

 

It’s these last two bullet items where we’ll focus (i.e. for now, we’ll just agree that our tables should have primary keys as part of good relational design). We’re looking for single atomic values that depend upon the key – hence the first item in our little jingle.

 

So here’s an entity to review for adherence to first normal form:

 

x1.jpg

So how does CUSOMER measure up? There is a primary key, and it’s a single numerical column – can’t get much shorter than that. And all of the columns have simple data types that appear to contain single values (i.e. no complex data types like varrays, nested tables or XML Types – where you’d have to ask some additional questions). So at first glance you might accept CUSTOMER as being in 1NF – but you very well could be wrong L

 

I actually testified as an expert witness in a court case on this first issue. See the LONG field – what and how is that being used for? After talking to the developers and looking at some code, assume that we find out that all the customer’s orders are collected here as the order number followed by a semi-colon. That’s an array – or a collection of values. That means we’re not in 1NF. And more importantly – we have a real world performace issue.

 

How would you write the query to join CUSTOMER and ORDER? Since SQL can not handle this – you’d have to programmatically process the join. So instead of the server doing the work on potentially large tables, the client code must process the join logic. Thus you have two bottlenecks – the client CPU and the network traffic to send all the records to the client to examine. Ouch!

 

OK – that example is a bit extreme. The real problem is with ADDRESS_LINE. In this case we have modeling how an address looks on an envelope rather than its constituent parts. Now in the case of an address that’s probably reasonable – but it sets a dangerous precedent. Look at the example below:

 

x2.jpg

 

The ORDER contains a bunch of dates. The idea is to track each step of processing the order from start to finish. So everytime we find a new “work in progress” step that we want to track, we need to alter the ORDER table and add an optional column. Yuck.

 

A more elegant solution that removes this hidden repeating group is to create a separate entity for work order tracking as shown here:

 

x3.jpg

 

Now we can add new new “work in progress” steps by merely adding a row of data to the ORDER_STAGES table. In fact, we can even build a maintenance screen to support this so that end-users can easily make such business oriented modifications without sending this back to information systems. In other words, the resulting system is more flexible.

 

Second Normal Form

 

There are two fundamental requirements for an entity or table table to meet second normal form – all of which must hold true:

 

  • The entity or table must be in first normal form
  • All attributes or columns must depend upon the whole key

 

It’s the last bullet item where we’ll focus. If the primary key is constructed upon a single attribute or column, then we are already in 2NF. But if the primary key is composed of multiple attributes or columns (i.e. a concatenated key), then we are looking for all the attributes or columns to depend upon the whole key – hence the second item in our little jingle.

 

So here’s an entity to review for adherence to second normal form:

 

x4.jpg

 

The test is really simple – does each attribute or column depend upon the whole key? First, does PROD_COST depend on both PROD_NAME and VENDOR_NAME? It might be arguable that this holds. But second, does FACTORY_LOCATION depend upon anything other than VENDOR_NAME? Since the answer is most likely not, then we separate this information into its own entity as shown here:

 

x5.jpg

 

The only major problem that I see more often than not is where people break a cardinal relational database design rule, and have a single attribute or column contain multiple pieces of information. For example, they might design an attribute or column called PRODUCT_KEY which is a VARCHAR2(20) column where the first 10 characters are the product’s name and the second 10 are the vendor’s name. Thus in effect they have a concatenated key that violates 2NF. My point is that you cannot effectively normalize when attributes or columns don’t represent a single concept – so please don’t do it.

 

Third Normal Form

 

There are two fundamental requirements for an entity or table table to meet third normal form – all of which must hold true:

 

  • The entity or table must be in second normal form (and thus also in 1NF)
  • All non-key attributes or columns must depend on nothing but the key

 

It’s the last bullet item where we’ll focus. Does every non-key attribute or column depend upon nothing but the key – hence the third item in our little jingle.

 

So here’s an entity to review for adherence to third normal form:

 

x6.jpg

 

Again the test is quite simple – does each non-key attribute or column depend only on PROD_NUMBER? The last two attributes or columns very clearly depend on just the PROD_CATEGORY, so it violates 3NF. Once again the solution is to simply separate this related information into its own entity as shown here:

 

x7.jpg

 

Higher Normal Forms

 

Most shops will be well served if they can regularly identify and correct for up to 3NF. The primary question of whether to normalize further really depends on whom you ask. The database designers and data architects might like to normalize further, but then they don’t have to write the more complex code to manage such highly structured data. There is a clear trade-off between code readability and maintainability when you go much higher than 3NF. But it’s my experience that most developers’ skills and patience rapidly erode beyond 3NF. We live in a world of do more faster and with less resources. So let’s not design a perfect database doomed to fail based upon academic or theoretical practice that sacrifices coding efficiency to save cheap disk space. Let’s choose a happy medium that’s effective and yet allows us to remain efficient in the implementation. And you heard that from a person with his PhD – so I guess 20 years in the trenches has forced me to practice what I preach.