Page 229 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes
Figure 13.3: Sales-monthly Segmentation
Disk 1

Jan PSU 1
January sales Feb PSU 1
Segment
Mar PSU 1
Disk 2

February sales Jan PSU 2
Segment Feb PSU 2
Mar PSU 2

Disk 3
March sales
Segment Jan PSU 3
Feb PSU 3
Mar PSU 3

13.2.1 Horizontal Partitioning

Horizontal partitioning a fact table speed up queries without indexing, by minimizing the set of
data to be scanned. It is not feasible to partition a fact table into segments (virtual object like
table) as we could not assure of each segment to be of same size as others, because the number
of transaction within the business at given point will vary it may between year to year.
Since business transaction volumes will be higher in seasons like festivals and occasions as
compared to the rest of year.
So we have to think all possible ways while partitioning the fact data at the same time we also
have to consider the requirements for manageability of the data warehouse.

Partitioning by Time into Equal Segments

This is a standard form of partitioning. Here partition of fact table is done on a time period basis,
where each time period represents a significance retention period within the business. Like if
maximum queries are on month-to-date values, then the partition is done into monthly segments
or if maximum query period is fortnight-to-date, consider partition into fortnightly segments
but we should consider the number of tables should not exceed the limit.

We can reuse table partitions by removing all the data from the partitions, by round robin by
the warehouse manager. It is better to consider that number of partitions will store transactions
over a busy period of the business.

Partitioning by Time into Different-sized Segments

This type of partitions is done where previous data is accessed infrequently. This will be as set
of small partitions for current data, large partition for active data and even larger partition for
inactive data.

222 LOVELY PROFESSIONAL UNIVERSITY