//|||| \\\       //  || \\  \\      //   || \\     //    ||   \\    //     ||     \\   ////////||      \\  //       || \\  \\\ //       ||||  \\\

ABLE Information Services Database Index Design

Course Description

TL120   Cost-Saving Database Index Design

   2 Days with case study Lab exercises *

Topics   

Case Study: Inadequate Indexing

• Sequential and Random reads
• QUBE - estimation of Touches
• The Index, the Fat Index and
      the Semi-Fat Index

Evaluating Indexing Alternatives

• Index Matching and Index Screening
• The impact of Predicate Filter Factor
• Difficult Predicates
• Quick Estimate for the Elapsed Time for various access paths
• Lab: Best index for two different cursors

Systematic Index Design

• The Three-Star Index and 2 Alternatives
• Lab: Candidates A and B
• Filter Factor Pitfall
• A Range of Improvements: Cost Options
• Lab: Design Cost-Effective Indexes for complex one-table SELECT's

Reactive Index Design: Improve Indexing of Operational Tables

• Identify culprits and victims
• Identify promising and unpromising culprits
• Real-life examples, Spike Report from trace
• Lab: Culprit or Victim?

A common and non-trivial problem:
    Design Indexes for a Nested Loop Join

• Nested Loop Join Access Paths
• Merge Scan / Hash Join
• Table design: denormalize
• Lab: Design indexes for a traditional Join

Unpredictable WHERE Clause Predicates

• Input Screens with Optional Predicate Values
• AND / OR: Multiple Index Access
• Bit map indexes: Advantages and restrictions
• Star Join and Data Warehouse Indexing
• Fact table and Dimension tables Indexes
• Summary tables

Index Free Space and Reorganization recommendations

• Insert patterns and Clustering
• Index Row size and Split Ratios
• Calculating Free Space for Indexes and Tables
• Calculating Reorganization Frequency

Optimizers are not perfect

• Bad Access Path or Bad Cost Estimates
• Helping the Optimizer: Hints and Statistics
• When Access Path is selected: Bind or Run
• Impact on Index Design

DBMS-specific options and restrictions and Computer-assisted index design

Note: A copy of Mr. Lahdenmäki’s textbook on relational database performance
is included in the course material.

* This course will also be offered in optional versions of 2.5 days and 3 days if requested.

See the benefits that a CIO or manager considers …    Consider in-house training     [v080311]