Physical Design

1
Physical Design

• Practical Issues of Data Placement

2
Access efficiency

• Driven by number of disk reads
• Join logic
• SELECT FROM A, B WHERE
• A.KEY B.KEY AND
• A.CONDITION TRUE
• Read Key A IF condition TRUE
• Read All Bs
• Match current A
• Read Next A

3
Improving Efficiency

• Indexing
• Locate specific records quickly
• Denormalization
• Reduce the number of joins
• Partitioning
• Reduce the amount of data

4
INDEXES PRIMARY KEY INDEXES

• Maintain record integrity by assuring that no
  duplicate values exist
• CREATE UNIQUE INDEX PRODINDEX ON PRODUCT
  (PRODUCT_NO)

5
IndexesNonkey Indexes

• Allow efficient access to files using non-key
  data.
• CREATE INDEX DESCRIP ON PRODUCT (DESCRIPTION)

6
IndexesClustering Indexes

• Determine the physical storage sequence for data
• CREATE INDEX DESCRIP ON PRODUCT (DESCRIPTION)
  CLUSTER

7
Denormalization

• Selective violations of normalization principles
  for access efficiency
• Entities with 11 relationships. It may be wise
  to combine these into a single table.

8
Denormalization

• MN relationships with non-key attributes
  (gerunds). Extracting attributes from one entity
  into another requires a join that accesses the
  link file and the associated entity. It may be
  worth violating 2NF or 3NF rules to duplicate
  commonly needed attribute values in the link file
  or even in the "parent" entity.

9
Denormalization

• Reference data. These are 1M relationships in
  which the 1 side is a lookup table for indicator
  keys in the "many" file. If these lookups are
  not used in many files and there are not many
  instances of the "many" entity for each "one"
  occurrence, it may be a good idea to move the
  reference attributes to the parent file and
  violate 3NF.

10
Partitioning

• Splitting data into groups for improved
  performance
• Vertical
• Select frequently used attributes for a primary
  table and transfer others to a sub-class file
  that is less frequently accessed
• Horizontal
• Duplication

11
Partitioning

• Splitting data into groups for improved
  performance
• Vertical
• Horizontal
• Select frequently used rows for a small table and
  remove others to another table.
• Duplication

12
Partitioning

• Splitting data into groups for improved
  performance
• Vertical
• Horizontal
• Duplication
• Create shadow files and store them at the
  location where they will be processed

13
Distributed Data (Maintaining Concurrency)

• Two Phased Commit
• Prepare commit by locking all versions
• Execute the commit simultaneously