Denormalization Techniques-Techniques for Data Warehousing-Lecture Slides, Slides of Basics of Data Warehousing

Download Denormalization Techniques-Techniques for Data Warehousing-Lecture Slides and more Slides Basics of Data Warehousing in PDF only on Docsity!

Ahsan Abdullah

2

De-normalization Techniques

Ahsan Abdullah

3

Splitting Tables

ColA^ ColB Table^ ColC

Vertical Split ColA^ ColB

ColA

ColC Table_v

Table_v

ColA^ ColB

ColC^ Horizontal split

ColA^ ColB

ColC

Table_h

Table_h

Ahsan Abdullah

5

Splitting Tables: Horizontal splitting ADVANTAGE  Enhance security of data.  Organizing tables differently for differentqueries.  Graceful degradation of database in case oftable damage

^ Fewer rows result in flatter B-trees and fast dataretrieval.

Ahsan Abdullah

6

Splitting Tables: Vertical Splitting  Infrequently accessed columns become extra“baggage” thus degrading performance. Very useful for rarely accessed large text columnswith large headers.  Header size is reduced, allowing more rows perblock, thus reducing I/O. Splitting and distributing into separate files withrepeating primary key.  For an end user, the split appears as a single tablethrough a view.

Ahsan Abdullah

8

Pre-Joining…

Tx_IDnormalized Sale_ID

Item_ID Item_Qty Sale_Rs Tx_ID^

Sale_ID^

Item_ID Item_Qty Sale_Rs Sale_date Sale_person Master Sale_ID Sale_date Sale_person denormalized

Detail

M

Ahsan Abdullah

9

Pre-Joining: Typical ScenarioTypical of Market basket queryJoin ALWAYS requiredTables could be millions of rowsSqueeze Master into DetailRepetition of facts. How much?Detail 3-4 times of master

Ahsan Abdullah

11

Adding Redundant Columns…

Columns can also be moved, instead of making themredundant. Very similar to pre-joining as discussedearlier. EXAMPLE Frequent referencing of code in one table andcorresponding description in another table. ^ A join is required. ^ To eliminate the join, a redundant attribute added inthe target entity which is functionally independent ofthe primary key.

Ahsan Abdullah

12

Redundant Columns: Surprise

Note that: ^ Actually increases in storage space, andincrease in update overhead. ^ Keeping the actual table intact andunchanged helps enforce RI constraint. ^ Age old debate of RI ON or OFF.