Which Of The Following Statements About Data Warehouses Is Not True?

iii
Physical Design in Data Warehouses

This chapter describes the physical blueprint of a data warehousing environment, and includes the following topics:

Moving from Logical to Physical Design
Physical Design

Moving from Logical to Physical Design

Logical design is what you draw with a pen and paper or design with Oracle Warehouse Architect or Designer before building your warehouse. Physical design is the creation of the database with SQL statements.

During the physical design process, you lot convert the data gathered during the logical design stage into a description of the concrete database construction. Physical pattern decisions are mainly driven by query performance and database maintenance aspects. For example, choosing a division strategy that meets common query requirements enables Oracle to take advantage of division pruning, a way of narrowing a search before performing it.

Physical Design

During the logical design phase, you defined a model for your information warehouse consisting of entities, attributes, and relationships. The entities are linked together using relationships. Attributes are used to describe the entities. The unique identifier (UID) distinguishes between ane instance of an entity and another.

Figure iii-1 offers you lot a graphical mode of looking at the different means of thinking about logical and concrete designs.

Effigy 3-ane Logical Design Compared with Physical Design

Text description of dwhsg006.gif follows
Text clarification of the illustration dwhsg006.gif

During the physical design process, you translate the expected schemas into bodily database structures. At this time, yous accept to map:

Entities to tables
Relationships to strange key constraints
Attributes to columns
Primary unique identifiers to chief key constraints
Unique identifiers to unique key constraints

Physical Design Structures

Once you accept converted your logical pattern to a physical one, you lot will need to create some or all of the post-obit structures:

Tablespaces
Tables and Partitioned Tables
Views
Integrity Constraints
Dimensions

Some of these structures crave disk space. Others be only in the data lexicon. Additionally, the following structures may be created for performance improvement:

Indexes and Partitioned Indexes
Materialized Views

Tablespaces

A tablespace consists of one or more datafiles, which are physical structures within the operating organization you are using. A datafile is associated with only i tablespace. From a blueprint perspective, tablespaces are containers for physical design structures.

Tablespaces need to exist separated past differences. For example, tables should be separated from their indexes and pocket-sized tables should be separated from large tables. Tablespaces should as well represent logical business units if possible. Considering a tablespace is the coarsest granularity for backup and recovery or the transportable tablespaces mechanism, the logical business blueprint affects availability and maintenance operations.

Tables and Partitioned Tables

Tables are the bones unit of measurement of data storage. They are the container for the expected amount of raw information in your data warehouse.

Using partitioned tables instead of nonpartitioned ones addresses the key problem of supporting very large data volumes by allowing you lot to decompose them into smaller and more manageable pieces. The main blueprint criterion for partitioning is manageability, though you lot will also meet functioning benefits in most cases considering of partition pruning or intelligent parallel processing. For example, you might choose a partitioning strategy based on a sales transaction date and a monthly granularity. If you accept iv years' worth of data, you can delete a month'southward data as it becomes older than 4 years with a single, quick DDL statement and load new data while merely affecting 1/48th of the consummate table. Business questions regarding the last quarter will but affect three months, which is equivalent to three partitions, or iii/48ths of the total volume.

Partitioning big tables improves functioning because each partitioned piece is more manageable. Typically, y'all partition based on transaction dates in a information warehouse. For example, each month, one month'due south worth of information tin be assigned its ain partition.

Data Segment Compression

You can salve deejay infinite by compressing heap-organized tables. A typical type of heap-organized tabular array you should consider for data segment pinch is partitioned tables.

To reduce disk use and memory utilise (specifically, the buffer enshroud), yous can shop tables and partitioned tables in a compressed format inside the database. This oftentimes leads to a better scaleup for read-only operations. Data segment compression can also speed up query execution. At that place is, however, a cost in CPU overhead.

Data segment pinch should be used with highly redundant data, such as tables with many strange keys. You should avoid compressing tables with much update or other DML activity. Although compressed tables or partitions are updatable, there is some overhead in updating these tables, and high update action may work against compression by causing some space to be wasted.

Views

A view is a tailored presentation of the data contained in one or more tables or other views. A view takes the output of a query and treats it every bit a table. Views do non require any infinite in the database.

Integrity Constraints

Integrity constraints are used to enforce business rules associated with your database and to prevent having invalid information in the tables. Integrity constraints in data warehousing differ from constraints in OLTP environments. In OLTP environments, they primarily prevent the insertion of invalid data into a tape, which is not a big problem in information warehousing environments because accurateness has already been guaranteed. In data warehousing environments, constraints are only used for query rewrite. NOT Nil constraints are particularly common in data warehouses. Under some specific circumstances, constraints need space in the database. These constraints are in the form of the underlying unique index.

Indexes and Partitioned Indexes

Indexes are optional structures associated with tables or clusters. In addition to the classical B-tree indexes, bitmap indexes are very common in data warehousing environments. Bitmap indexes are optimized index structures for prepare-oriented operations. Additionally, they are necessary for some optimized data admission methods such as star transformations.

Indexes are just like tables in that you can partition them, although the sectionalisation strategy is not dependent upon the table structure. Partitioning indexes makes it easier to manage the warehouse during refresh and improves query performance.

Materialized Views

Materialized views are query results that have been stored in advance and then long-running calculations are non necessary when yous actually execute your SQL statements. From a physical design point of view, materialized views resemble tables or partitioned tables and conduct like indexes.

Dimensions

A dimension is a schema object that defines hierarchical relationships between columns or column sets. A hierarchical relationship is a functional dependency from 1 level of a hierarchy to the next one. A dimension is a container of logical relationships and does not require whatsoever space in the database. A typical dimension is urban center, state (or province), region, and country.