Databases Reference
In-Depth Information
-
Hash: range partitioning:
a.
First partition of the table by hash keys.
b.
Subpartition by range of values.
-
Range: hash partitioning:
a.
First partition of the table by a range of values.
b.
Subpartition by hash keys.
The partitioning techniques introduce a different problem in the storage architecture, the skewing
of the database. Certain partitions may be very large and others small, and this will generate subopti-
mal execution plans.
The following are partitioning methods that are used for large tables:
●
Vertical partitioning:
●
Partition large tables by columns across the database, normally in the same database.
●
The biggest issue with this technique is that we have to balance the partition when the tables
grow in columns.
●
Queries needing large columns will fail to perform.
●
The technique will not scale for data warehousing, but lends well for OLTP.
●
Horizontal partitioning:
●
Tables are partitioned by rows and distributed across servers or nodes in a database.
●
Queries looking for more than one group of rows will have to read multiple nodes or servers.
●
Extremely large tables will be heavily skewed in this distribution.
●
Moderately sized tables will perform well.
Partitioning, adding infrastructure, and optimizing queries do not enable unlimited scalability in
the data warehouse for extremely large data. The workarounds used to enhance scalability and perfor-
mance include:
●
Designing and deploying multiple data warehouses for different business units or line of business
(defeats the purpose of an integrated data warehouse).
●
Deploy multiple datamarts (increases architecture complexity).
●
Deploy different types of databases to solve different performance needs (very
maintenance-prone).
The evolution of the data warehouse appliance and cloud and data virtualization has created a new
set of platforms and deployment options, which can be leveraged to reengineer or extend the data
warehouse for sustained performance and scalability. We will be looking at these technologies and the
reengineering techniques in the next few chapters.
Architecture approaches to building a data warehouse
The last area of overview in this chapter is the data warehouse building approaches with different
architecture styles. In the data warehouse world today, there are two schools of thought in the archi-
tecture approach for building and deploying a data warehouse:
1.
Information factory is a widely popular model conceived and designed by Bill Inmon. It uses
a data modeling approach aligned with the third normal form where the data is acquired at
