This presentation will discuss many issues surrounding alternative table designs and performance. We will look at many different alternatives to help design tables for the best performance possible for the type of application process. There are many issues to consider for high volume insert and update transactions, and a balancing act to be achieved for responsiveness to readers. Table issues such as denormalization, non-column attributes, indicator columns, repeating columns, derived data, aggregated columns, table flipping, identify columns, surrogate keys, horizontal and vertical partitioning. We will also look at issues with designing VLTBs (Very Large Tables) and XVLTBs (eXtremely Large Tables) – the rules have changed! We also look at index challenges.
Creating Tables to Support High Volume Inserts
Alternate Column Designs for Application Performance
Building VTLBs and XVLTBs
Index Impacts and Usage – NPSIs and DPSIs
I.Alternative table designs and performance.
a.Design tables for the best performance possible for the type of application process.
b.Issues to consider for high volume insert and update transactions
c.Performing a balancing act to be achieved for responsiveness to readers.
II.Table issues
a.Denormalization
b.Non-column attributes
c.Indicator columns
d.Repeating columns
e.Derived data
f.Aggregated columns
g.Table flipping
h.Identity columns
i.Surrogate keys
j.Horizontal and vertical partitioning
k.Declared Temporary Tables
III.Designing VLTBs (Very Large Tables) and XVLTBs (eXtremely Large Tables)
a.Issues that are different from traditional design
b.What works and what does not
c.Purging and Archiving Issues
d.Using Additional Tables for Access
e.Actual client experience stories
IV.Index Issues
a.Challenges with NPSIs
b.Challenges with DPSIs
c.Uniqueness and Clustering
HR
EN
SL