Oracle Tips by Burleson

Chapter 1 – Introduction to Oracle Physical Design

statements such as “It doesn’t make sense to dismantle your car every time you are done driving it and rebuild the car each time you want to drive it.”)

Figure 1.5 - A Nested table

Oracle has moved toward allowing complex objects to have a concrete existence. In order to support the concrete existence of complex objects, Oracle introduced the ability to build arrays of pointers with row references directly to Oracle tables. Just as a C++ program can use the char** data structure to have a pointer to an array of pointers, Oracle allows similar constructs whereby the components of the complex objects reside in real tables with pointers to the subordinate objects. At runtime, Oracle simply needs to dereference the pointers, and the complex object can be quickly rebuilt from its component pieces.

Also, notice that sometimes repeating groups are derived from the sum of other values in the transaction relation. In those cases, we must make a conscious decision whether to redundantly store these summations or have Oracle compute them at runtime.   Some shops even use nested table as an adjunct to the first normal form representation of their data (Figure 1.6).  Here we see that the student-grade relationship is accessible both with a standard relational join into the grade table, or by access via the nested table.

Figure 1.6 – Concurrent 0NF and 1NF data representation

First Normal Form (1NF)

In essence, any relation is in first normal form if it does not contain any repeating values. Here, we have taken our relations with repeating values and moved them to separate relations. When the new relations are created, we carry the primary key of the original relation into the new relation.

From a physical design perspective, a first-normal form relation implied that a table join will be required whenever the repeating groups are required in an SQL query.  From a performance perspective, the table can be kept inside the base entity with VARRAY tables or nested tables.  Another popular approach is to pre-join the tables using materialized views, thereby fetching the base data and the repeating groups in a single disk I/O.

For de-normalization in data warehouses, first normal form tables are the most popular representation for fact tables (Figure 1.7).

Figure 1.7 – A  first-normal form schema for a data warehouse

This first normal form data representation is also popular for multidimensional data representation in data warehouse applications, where the end-user tools allows the end-users to roll-up data according to their specifications.  In Oracle, the Oracle Express engine provides multi-dimensional representation, and the database must be sufficiently denormalized to quickly provide these views of the database.  Figure 1.8 shows an Oracle9i external table mapped into a spreadsheet for multidimensional analysis as a MS-Excel pivot table.

Figure 1.8 – Using a Oracle9i external table as a spreadsheet for data

Second Normal Form

The purpose of the second normal form (2NF) test is to check for partial key dependencies. Partial key dependencies are created when we break off an Un-normalized relation into first normal form by carrying the key, thereby creating a concatenated key with several data items. The formal definition of second normal form is as follows: