|Oracle8i Data Warehousing Guide
Release 2 (8.1.6)
This chapter describes physical design in a data warehousing environment, and includes the following:
In a sense, logical design is what you draw with a pencil before building your warehouse and physical design is when you create the database with SQL statements.
During the physical design process, you convert the data gathered during the logical design phase into a description of the physical database, including tables and constraints. Physical design decisions, such as the type of index or partitioning have a large impact on query performance. See Chapter 6, "Indexes" for further information regarding indexes. See Chapter 5, "Parallelism and Partitioning" for further information regarding partitioning.
Logical models use fully normalized entities. The entities are linked together using relationships. Attributes are used to describe the entities. The UID distinguishes between one instance of an entity and another.
A graphical way of looking at the differences between logical and physical designs is in Figure 3-1:
Physical design is where you translate the expected schemas into actual database structures. At this time, you have to map:
You will have to decide whether to use a one-to-one mapping as well.
Translating your schemas into actual database structures requires creating the following:
Tablespaces need to be separated by differences. For example, tables should be separated from their indexes and small tables should be separated from large tables. See Chapter 4, "Hardware and I/O", for further information regarding tablespaces.
Partitioning large tables improves performance because each partitioned piece is more manageable. Typically, you partition based on transaction dates in a data warehouse. For example, each month. This month's worth of data can be assigned its own partition. See Chapter 5, "Parallelism and Partitioning", for further details.
Data warehouses' indexes resemble OLTP indexes. An important point is that bitmap indexes are quite common. See Chapter 6, "Indexes", for further information.
Constraints are somewhat different in data warehouses than in OLTP environments because data integrity is reasonably ensured due to the limited sources of data and because you can check the data integrity of large files for batch loads. Not null constraints are particularly common in data warehouses. See Chapter 7, "Constraints", for further details.