SQL Tuning [Electronic resources] نسخه متنی

parallel execution plans
2nd

parentheses (())

in FROM clause for DB2

surrounding ordered list of items

partitioned tables

path to data
[See data access; execution plans]

performance
[See also SQL tuning]

adding indexes affecting

caching scheme affecting

calculations performed on data affecting

compared to throughput

database operations affecting

logical I/Os and

physical layout of table rows and

SQL as factor in

physical I/O

caching used to minimize

performance of

PLAN_TABLE table

plus sign (+)

in comment

in join clause

post-group filtering, not included in query diagram

post-read filter
2nd
3rd

primary keys

indicated by arrows in query diagrams

missing from join

problem cases
[See also exercises; SQL tuning]

abnormal outer joins

all tables relatively small except one

Cartesian products between first set of rows

corner-case problems
2nd
3rd
4th

cyclic join graphs

detail join ratios close to 1.0

detail join ratios less than 1.0
2nd

disconnected query diagrams

filter ratios close to each other

join ratios less than 1.0

joining to the same table twice

joins with no primary key

large detail join ratios

large rowcount returned by query

master join ratios less than 1.0
2nd

middleware processes handling too many rows

one-to-one joins
2nd

outer joins to views

performance and throughput problems

queries aggregating too many rows
2nd
3rd

queries repeated a large number of times
2nd

queries returning too many rows
2nd

queries running slowly even when returning few rows
2nd

queries with set operations

queries with subqueries
2nd

queries with views
2nd

query diagrams with multiple roots

redundant reads in view-using queries

unfiltered joins

unnecessary nodes and joins

programmers, tuning their own SQL

pure logical I/O, caching used for

purge eldest pattern

purge, not by age pattern

PUSH_SUBQ hint