DB2 SQL TUNING-1 - Topics of Discussion General...

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

View Full Document Right Arrow Icon
Background image of page 1

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

View Full DocumentRight Arrow Icon
Topics of Discussion General Recommendation Predicates Evaluation Filter Factor Column Correlation Writing Subquery Special Techniques to influence access path Using DB2 EXPLAIN Different Access Types
Background image of page 2
Topics of Discussion Join Methods DB2 Data and Index page prefetch Sorting of Data and RIDs View Merge/Materialization Query Parallelism DB2 V5 Features
Background image of page 3

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

View Full DocumentRight Arrow Icon
General Recommendation Make Sure Queries are as simple as possible Unused rows are not fetched. Filtering to be done by DB2 not in the application program. Unused columns are not selected There is no unnecessary ORDER BY or GROUP BY Clause Use page level locking and try to minimize lock duration. Big tables should be handled with care.
Background image of page 4
General Recommendation Try to use indexable predicates wherever possible Do not code redundant predicates Make sure that declared length of host variable is not greater than length attribute of data column. If there are efficient indexes available on the tables in the subquery, co-related subquery will perform better. Otherwise no co related subquery will perform better. If there are multiple subqueries, make sure that they are ordered in efficient manner.
Background image of page 5

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

View Full DocumentRight Arrow Icon
Predicate Predicates are found on WHERE, ON and HAVING clause of the SQL. ON predicates are applied first, then WHERE predicates and after data access HAVING predicates are applied. Predicates on HAVING clause are not used when
Background image of page 6
Image of page 7
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 11/24/2011 for the course FINANCE 123 taught by Professor Hbr during the Spring '11 term at Lethbridge College.

Page1 / 17

DB2 SQL TUNING-1 - Topics of Discussion General...

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

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