DB2 SQL TUNING-2 - Using Index Index to be defined should...

Info iconThis preview shows pages 1–8. Sign up to view the full content.

View Full Document Right Arrow Icon
Using Index Index to be defined should be solely based on how does application fetch data Proper definition of index will avoid sort You need to trade cost of defining index and performance.
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Matching Index Scan Match index scan provide filtering This is possible if predicates are specified on either the leading or all of the index key columns If degree of filtering is high. Matching index scan is efficient MATCHCOLS will provide the number of matching columns If there are more than one index, DB2 will use IX with most restrictive filtering for matching index scan
Background image of page 2
Non-Matching IX scan This is also called Index Screening DB2 select index screening when predicates are specified on index key columns but are not part of the matching columns Index screening predicates improve the index access by reducing the number of rows that qualify while searching the index MATCHCOLS = 0 and ACCESSTYPE = I
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
IN LIST Index Scan An IN-list index scan is a special case of the matching index scan, in which a single indexable IN predicate is used as a matching equal predicate. PLAN TABLE shows MATCHCOLS > 0 and ACCESSTYPE = N
Background image of page 4
Multiple Index Scan Multiple index access uses more than one index to access a table It is a good access path when No single index provides efficient access OR A combination of index accesses provides efficient access . LIST Sequential prefetch is used as RIDs are collected from each index scan ACCESSTYPE = M,MI,MU,MX and PREFETCH = L Same index also may be scanned more than ones
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
One Fetch Access One-fetch index access requires retrieving only one row. It is the best possible access path if available. One-fetch index access is a possible when : There is only one table in the query. The column function is either MIN or MAX and There is an ascending index column for MIN, and a descending index column for MAX. Either no predicate or all predicates are matching predicates for the index. And There is no GROUP BY.
Background image of page 6
With index-only access, the access path does not require any data pages because the access information is available in the index Because the index is almost always smaller than the table itself, an index-only access path usually processes the data efficiently .
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 8
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 28

DB2 SQL TUNING-2 - Using Index Index to be defined should...

This preview shows document pages 1 - 8. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online