Chapter 4. Deriving the Ideal Index for a SELECT
Published Online: 27 JAN 2005
DOI: 10.1002/0471721379.ch4
Copyright © 2005 John Wiley & Sons, Inc.
Book Title
Relational Database Index Design and the Optimizers: DB2, Oracle, SQL Server, et al.
Additional Information
How to Cite
Lahdenmäki, T. and Leach, M. (2005) Deriving the Ideal Index for a SELECT, in Relational Database Index Design and the Optimizers: DB2, Oracle, SQL Server, et al., John Wiley & Sons, Inc., Hoboken, NJ, USA. doi: 10.1002/0471721379.ch4
Publication History
- Published Online: 27 JAN 2005
- Published Print: 24 JUN 2005
ISBN Information
Print ISBN: 9780471719991
Online ISBN: 9780471721376
- Summary
- Chapter
Keywords:
- performance guidelines;
- performance factors;
- table scans;
- index scans;
- random read times;
- sequential read times;
- CPU costs;
- index stars;
- index design;
- three star index;
- ideal index;
- fat index;
- semi-fat index;
- index design algorithm;
- best index design;
- practical index design;
- CPU time;
- disk read time;
- elapsed time;
- index changes;
- maintenance overheads;
- response time;
- drive load;
- disk costs;
- recommendations
Summary
Guidelines for the major performance factors concerning table and index scans; random and sequential read times and CPU costs. Assigning stars to an index for a SELECT statement, according to the three most important requirements; the design of a three star index - the ideal index for the statement; fat indexes. An algorithm to design the best index for a SELECT statement; consideration of the existing indexes to determine the most practical index, taking into account CPU time, disk read time and elapsed time. The implications of any suggested changes to the current indexes with regard to the maintenance overheads; response time, drive load and disk costs; recommendations.