Databases Reference
In-Depth Information
6.7.2 Materialized View Replication and Other
Duplication Techniques
Because an individual table can only be partitioned one way, there will be times when a
column used in a join with one table is a poor partitioning choice when joining with
another table. Similarly, there will be times when columns used in equality predicates
are not ideal to satisfy join collocation. At first glance this appears to be a case of picking
the lesser of several evils. There are, however, techniques that can be used to create alter-
native partitioning of data. These come in the following forms:
1.
Replicated tables.
The ability to locate an entire table on all nodes, so that every
node contains 100% of the table data.
2.
Repartitioned materialized views.
Using materialized views to create an alternate
partitioning of data stored in base tables.
3.
Replicated materialized views.
The ability to locate an entire materialized view
on all nodes, so that every node contains 100% of the materialized view. The
view itself may include only a subset of the columns from the base table, as well
as possible aggregation.
4.
Repartitioned indexes.
The ability to create indexes that have distinct partition-
ing from their parent table.
5.
Global join indexes.
A special class of repartitioned indexes that includes record
identifier (RID) access back to the records in the base tables from which it is
derived.
Each of these techniques has benefits and detriments, which will be introduced
here. In the following sections, let's consider an employee table with columns:
EMP_ID, SIN, DIVISION, DEPARTMENT, JOB_ID,
SALARY, REGION
The base table may be partitioned perhaps on EMP_ID, which is useful for several
joins. However, there may be some joins on JOB_ID as well, and the database designer
is trying to exploit alternative techniques to achieve good collocation for the joins on
JOB_ID without disruption of the excellent collocation that exists for joins on
EMP_ID.
Replicated Tables
Replicated tables are a powerful technique for avoiding collocation problems. Using this
technique all records for a given table are stored on every node in the MPP that the
table is defined on. As a result, if there are
n
nodes in the MPP, then approximately
n
-
