This chapter explains the physical design of a file warehousing atmosphere, and includes the complying with topics:

Moving from Logical to Physical Design

Logical style is what you draw via a pen and paper or design via Warehome Builder or Designer before building your data warehome. Physical architecture is the production of the database through SQL statements.

You are watching: A key decision in the physical design process is:

Throughout the physical design process, you convert the information gathered throughout the logical style phase into a summary of the physical database framework. Physical style decisions are greatly pushed by query performance and also database maintenance facets. For instance, selecting a partitioning strategy that meets widespread query requirements allows Database to take benefit of partition pruning, a means of narrowing a search before performing it.

Physical Design

During the logical style phase, you characterized a version for your data warehome consisting of entities, attributes, and relationships. The entities are connected together utilizing relationships. Attributes are supplied to describe the entities. The distinct identifier (UID) distinguishes between one circumstances of an entity and one more.

Figure 3-1 illustrates a graphical means of distinguishing in between logical and also physical deindicators.

Figure 3-1 Logical Design Contrasted with Physical Design

Description of "Figure 3-1 Logical Deauthorize Contrasted through Physical Design"

During the physical design process, you translate the intended schemas right into actual database structures. Currently, you have to map:

Entities to tables

Relationships to foreign crucial constraints

Attributes to columns

Primary unique identifiers to primary vital constraints

Unique identifiers to distinctive essential constraints

Physical Design Structures

Once you have actually converted your logical style to a physical one, you will have to produce some or all of the adhering to structures:

Some of these structures need disk room. Others exist just in the information dictionary. Furthermore, the adhering to structures may be developed for performance improvement:


A tableroom consists of one or even more datadocuments, which are physical frameworks within the operating system you are making use of. A datafile is connected through just one tablespace. From a design perspective, tablespaces are containers for physical style structures.

Tablespaces must be separated by distinctions. For instance, tables should be separated from their indexes and small tables must be separated from big tables. Tablespaces should also reexisting logical organization devices if possible. Since a tablespace is the coarsest granularity for backup and recoexceptionally or the transportable tablespaces device, the logical company architecture affects availability and maintenance operations.

You can currently usage ultrabig information records, a significant innovation in extremely big databases.

See Also:

Chapter 4, "Hardware and also I/O Considerations in Documents Warehouses" for indevelopment about tablespaces

Tables and Partitioned Tables

Tables are the standard unit of information storage. They are the container for the supposed amount of raw information in your data warehouse.

Using partitioned tables instead of nonpartitioned ones addresses the crucial difficulty of supporting exceptionally huge data volumes by allowing you to divide them right into smaller sized and also even more manageable pieces. The major style criterion for partitioning is managecapacity, though you will also check out performance benefits in the majority of instances because of partition pruning or intelligent parallel handling. For instance, you could select a partitioning strategy based on a sales transactivity date and also a monthly granularity. If you have actually 4 years" worth of data, you have the right to delete a month"s data as it becomes older than 4 years through a solitary, quick DDL statement and also load brand-new data while just affecting 1/48th of the complete table. Firm questions about the last quarter will only influence 3 months, which is indistinguishable to three partitions, or 3/48ths of the total volume.

Partitioning big tables enhances performance because each partitioned piece is even more controlled. Normally, you partition based on transactivity dates in a file warehome. For instance, each month, one month"s worth of data deserve to be assigned its very own partition.

Table Compression

You have the right to conserve disk room by compressing heap-organized tables. A typical type of heap-arranged table you need to take into consideration for table compression is partitioned tables.

To minimize disk usage and memory usage (particularly, the buffer cache), you can save tables and also partitioned tables in a compressed format inside the database. This frequently leads to a much better scaleup for read-only operations. Table compression have the right to likewise rate up query execution. Tright here is, but, a cost in CPU overhead.

Table compression must be used through very redundant data, such as tables through many foreign keys. You should prevent compressing tables through much upday or various other DML activity. Although compressed tables or partitions are updatable, tbelow is some overhead in updating these tables, and also high upday activity might work-related against compression by bring about some space to be wasted.

See Also:

Chapter 5, "Partitioning in File Warehouses" and Chapter 15, "Maintaining the Data Warehouse"


A watch is a tailored presentation of the data contained in one or even more tables or various other views. A check out takes the output of a query and treats it as a table. Views carry out not need any kind of area in the database.

Integrity Constraints

Integrity constraints are offered to enpressure service rules connected through your database and also to proccasion having actually invalid indevelopment in the tables. Integrity constraints in data warereal estate differ from constraints in OLTP settings. In OLTP atmospheres, they primarily prevent the insertion of invalid information into a document, which is not a huge trouble in information warereal estate atmospheres because accuracy has already been guaranteed. In data warereal estate atmospheres, constraints are just offered for query recompose. NOT NULL constraints are especially prevalent in information warehomes. Under some specific situations, constraints need room in the database. These constraints are in the form of the underlying unique index.

Indexes and Partitioned Indexes

Indexes are optional structures linked with tables or clusters. In addition to the timeless B-tree indexes, bitmap indexes are extremely widespread in data warehousing atmospheres. Bitmap indexes are optimized index frameworks for set-oriented operations. Furthermore, they are important for some optimized data accessibility methods such as star changes.

Indexes are simply choose tables in that you can partition them, although the partitioning strategy is not dependent upon the table framework. Partitioning indexes provides it easier to manage the information wareresidence in the time of refresh and also boosts query performance.

Materialized Views

Materialized views are query outcomes that have been stored in development so long-running calculations are not vital once you actually execute your SQL statements. From a physical architecture point of view, materialized views resemble tables or partitioned tables and also behave choose indexes in that they are used transparently and boost performance.

See more: Nursing Homes In Wake Forest Nc, Find Nursing Homes Near Wake Forest, Nc


A measurement is a schema object that defines ordered relationships between columns or column sets. A hierarchical connection is a useful dependency from one level of a hierarchy to the following one. A measurement is a container of logical relationships and does not require any kind of space in the database. A typical dimension is city, state (or province), region, and also country.