Physical Data Modelling

Partitioning Techniques

Partitioning means deliberate splitting of a table and its index data into manageable parts. The DBMS supports and provides the mechanism for partitioning. When you define the table, you can define the partitions as well. Each partition of a table is treated as a separate object. As the volume increases in one partition, you can split that partition further. The partitions are spread across multiple disks to gain optimum performance. Each partition in a table may have distinct physical attributes, but all partitions of the table have the same logical attributes.

Vertical Partitioning

In vertical partitioning, you separate out the partitions by grouping selected columns together. Each partitioned table contains the same number of rows as the original table. Usually, wide dimension tables are candidates for vertical partitioning.

Horizontal Partitioning

In horizontal partitioning, you divide the table by grouping selected rows together

Pros

• A query needs to access only the necessary partitions. Applications can be given the choice to have partition transparency or they may explicitly request an individual partition. Queries run faster when accessing smaller amounts of data.
• An entire partition may be taken off-line for maintenance. You can separately schedule maintenance of partitions. Partitions promote concurrent maintenance operations.
• Index building is faster.
• Loading data into the data warehouse is easy and manageable.
• Data corruption affects only a single partition. Backup and recovery on a single partition reduces downtime.
• The input–output load gets balanced by mapping different partitions to the various disk drives

‍

Materialised Views

A materialised view is a database object that contains the result of a precomputed query. Unlike regular (virtual) views, which are defined by a query but do not store the actual data, materialised views store the data resulting from the execution of a query. This precomputed or materialised data is periodically refreshed to reflect changes in the underlying data.

More advanced form of ((20230911175655-ia0heej “Pre-Join Denormalisation”)) and ((20230911175705-ano618i ‘Pre-Aggregation’))

Materialised before run-time, thus Permanent Storage is required

Access is given on logical structure but behind the scenes Physical Design / Structure is used to satisfy the query by the query optimiser

Any activity that can be avoided at runtime contributes to the overall efficiency.

Materialised Views help us just with that

What do they do better

• Transparency

  • Users and Applications should not be aware of change in architecture

• Auto Data Consistency

  • Maintenance automatically done by the DBMS

    • Maintenance efficiency is improved
    • DBMS can also defer (delay) MV updating based on the peak time

• Provides DBA & DW Architect with Freedom and Flexibility

• You can avoid a lot of computing at runtime

Traditional Views

AKA Virtual Tables

A window that projects the state of a database relation / table

• Created at Runtime
• Requires storage to store in runtime, No Permanent Storage

Purpose

Purpose is to restrict data access from user groups

Joining Techniques

Indexing Techniques