Index

Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

Index

A note on the digital index

A link in an index entry is displayed as the section title in which that entry appears. Because some sections have multiple index markers, it is not unusual for an entry to have several links to the same section. Clicking on any link will take you directly to the place in the text in which the marker appears.

Symbols

() (parentheses)

in FROM clause for DB2, Prevent too many outer joins from parsing at once
surrounding ordered list of items, Conventions Used in This Book

+ (plus sign)

in comment, General hint syntax
in join clause, Filtered outer joins

<> (angle brackets), surrounding missing portions of SQL statement, Conventions Used in This Book

“E” in query diagram, Diagramming EXISTS subqueries

“F” in query diagrams, Simplified Query Diagrams

“f” in query diagrams, Simplified Query Diagrams

“R”, in query diagram, Underlined numbers

A

aggregation, Ordering and aggregation, Glossary

(see also grouping; post-group filtering)
not included in query diagrams, Ordering and aggregation
queries aggregating too many rows, Unsolvable Problems, Aggregations of Many Details
in reports, Report information types, Report information types

ALL_ROWS hint, Approaches to tuning with hints

AND-EQUAL MERGE operation, Combining Indexes

angle brackets (<>), surrounding missing portions of SQL statement, Conventions Used in This Book

anti-join, Diagramming NOT EXISTS subqueries, Glossary

apples-and-oranges tables, Glossary

application, Why Tune SQL?

(see also queries)
altering, determining necessity of, Outside-the-Box Solutions, Unsolvable Problems, When Very Fast Is Not Fast Enough, Queries that Return Data from Too Many Rows, Tuned Queries that Return Few Rows, Slowly
altering, example of, Altering the SQL to Enable the Best Plan, Altering the Application
caching in, Caching to Avoid Repeated Queries, Caching to Avoid Repeated Queries
data volume produced by, Why Tune SQL?, When Very Fast Is Not Fast Enough, Queries that Return Data from Too Many Rows–Solutions
transferring data between applications, Middleware Processes Handling Too Many Rows

arithmetic expressions, indexes disabled when using, Enabling Use of the Index You Want

arrow

absence of, at ends of link, Case 3: Two-node filter (nonunique on both ends) between nodes is already linked through normal joins
at end of link, Links
at midpoint of link, Change focus and repeat, Diagramming EXISTS subqueries

audience for this book, Audience for This Book

author, contact information for, Comments and Questions

B

B-tree indexes, B-Tree Indexes, Glossary

batch reports, large, Large Batch Reports–Solutions

Beaulieu, Alan (Mastering Oracle SQL), Objectives of This Book

benefit-to-cost ratio, Choosing the Next Table to Join, When to Choose Early Joins to Upstream Nodes

bind variable peeking, Oracle cost-based optimizer prerequisites

bit-mapped indexes, Bit-Mapped Indexes

blind queries, Large Online Queries

block buffer cache, Caching in the Database, Glossary

blocks, Tables, Glossary

branch, B-Tree Indexes, Glossary

cold, Caching in the Database, Glossary

hot, Caching in the Database, Glossary

caching of, Caching in the Database, Caching in the Database
choosing for performance, Caching in the Database
hot data grouped in, Caching in the Database
indexed access and, Choosing Between a Full Table Scan and Indexed Access
physical I/O on, Caching in the Database
query causing, Caching in the Database

leaf, B-Tree Indexes, Glossary

LRU (least recently used), Caching in the Database

MRU (most recently used), Caching in the Database

root, B-Tree Indexes, Glossary

books

about Oracle Performance, Missing Indexes
about Oracle SQL, Objectives of This Book

branch blocks, B-Tree Indexes, Glossary

C

cache-hit ratio, Continuous Growth, Glossary

caching, Caching in the Database–Caching in the Database

in application, Caching to Avoid Repeated Queries, Caching to Avoid Repeated Queries
B-tree indexes and, B-Tree Indexes
block buffer cache, Glossary
continuous growth pattern and, Continuous Growth
full table scans and, Full Table Scans
indexed table access and, Choosing Between a Full Table Scan and Indexed Access
LRU caching, Caching in the Database, Glossary
performance effects of, Caching in the Database
purge eldest pattern and, Purge Eldest
purge, not by age pattern and, Purge, Not by Age
self-caching, Caching in the Database, Glossary

calcuations (see benefit-to-cost ratio; cache-hit ratio; correlation preference ratio; join ratio; filter ratio)

capitalized text used in this book, Conventions Used in This Book

Cartesian join, Join Types, Glossary

Cartesian product, Glossary

optimizing execution plan for, Special Rules for Special Cases, Safe Cartesian Products–Safe Cartesian Products
resulting from absence of join, Disconnected Query Diagrams–Disconnected Query Diagrams
resulting from query with multiple roots, Query Diagrams with Multiple Roots, Query Diagrams with Multiple Roots, Case 2: Breaking the Cartesian product into multiple queries

CBO (see cost-based optimizer)

Central Processing Unit (see CPU)

cloud of nodes, Standard Heuristic Join Order

COALESCE() function, Preventing Use of the Wrong Indexes

cold blocks, Caching in the Database, Glossary

columns

relevance to SQL tuning, Select lists
statistics for, used by cost-based optimizer, Providing the Cost-Based Optimizer with Good Data

comments about this book, sending, Comments and Questions

complete purge and regrowth pattern, Complete Purge and Regrowth

complex queries, Diagramming and Tuning Complex SQL Queries, Glossary

abnormal outer joins in, Outer Joins–Outer joins to a detail table with a filter
cyclic joins in, Cyclic Join Graphs–Cyclic join summary
disconnected queries, Disconnected Query Diagrams–Disconnected Query Diagrams
joins with no primary key in, Joins with No Primary Key
multiple roots in, Query Diagrams with Multiple Roots–Case 4: Converting an existence check to an explicit subquery
one-to-one joins in, One-to-One Joins–Conventions to display one-to-one joins
set operations in, Queries with Set Operations
subqueries in, Queries with Subqueries
views in, Queries with Views–Unnecessary nodes and joins

conditions (see filters; joins; selectivity)

contact information for this book, Comments and Questions

continuous growth pattern, Continuous Growth

conventions used in this book, Conventions Used in This Book

corner-case problems, A Bonus

abnormal query diagrams indicating, Interpreting Query Diagrams
changing functionality for, Outer joins to views
view-using queries causing, Queries with Views

correlated subqueries, forcing evaluation of, Execution-order hints

correlation joins, Forcing Execution Order for Outer Queries and Subqueries, Glossary

anti-join, Diagramming NOT EXISTS subqueries, Glossary
in query diagrams, Diagramming Queries with Subqueries
semi-join, Diagramming EXISTS subqueries–Diagramming EXISTS subqueries, Glossary

correlation preference ratio, Diagramming EXISTS subqueries, Diagramming EXISTS subqueries, Glossary

cost-based optimizer, Controlling Plans on Oracle, Controlling Plans on Oracle

choosing, Controlling the Choice of Oracle Optimizer–Controlling the Choice of Oracle Optimizer
controlling execution plans with, Controlling Oracle Cost-Based Execution Plans–Example
response to varying table and index sizes, Absolute table sizes (as opposed to relative sizes)
rule-based optimizer used with, Controlling the Choice of Oracle Optimizer
statistics affecting, Providing the Cost-Based Optimizer with Good Data, Fooling the Cost-Based Optimizer with Incorrect Data

CPU (Central Processing Unit), Glossary

cyclic join graph, Cyclic Join Graphs–Cyclic join summary, Glossary

D

data

amount produced by application, Why Tune SQL?, When Very Fast Is Not Fast Enough, Queries that Return Data from Too Many Rows–Solutions
denormalized, Glossary
normalized, Glossary
path to (see data access; execution plans)
transferring between applications, Middleware Processes Handling Too Many Rows

data access

caching and, Caching in the Database–Caching in the Database
indexes and, Indexes–Index Costs, Index-Organized Tables, Bit-Mapped Indexes
joins and, Joins–Join methods summary
reasons to understand, Data-Access Basics
selectivity, Calculating Selectivity–Combining Indexes
single-table access paths, Single-Table Access Paths–Choosing Between a Full Table Scan and Indexed Access
tables and, Tables–Complete Purge and Regrowth, Index-Organized Tables, Single-Table Clusters, Multitable Clusters, Partitioned Tables

data type conversions, indexes enabled and disabled by, Index Range-Condition Selectivity, Enabling Use of the Index You Want

database, Tables

(see also caching; indexes; tables)

adding merged join and filter indexes to, Merged Join and Filter Indexes–Merged Join and Filter Indexes

adding missing indexes to, Missing Indexes

adding table to, for joins with hidden join filters, One-to-one joins with hidden join filters in both directions

altering, example of, Altering the Database to Enable the Best Plan

combining tables in

for exact one-to-one joins, Exact one-to-one joins
for joins with hidden join filters, One-to-one joins with hidden join filters in both directions

separating tables in, for one-to-one join to small subset, One-to-one join to a much smaller subset

database blocks (see blocks)

database vendors

SQL tuning and, Preface, Introduction
tools for execution plans provided by, Viewing and Interpreting Execution Plans, Reading DB2 Execution Plans

date-type columns in indexes, Index Range-Condition Selectivity

DB2

COALESCE() function, Preventing Use of the Wrong Indexes

execution plans

complex, Complex Execution Plans
controlling, Controlling Plans on DB2–Let DB2 know when to optimize the cost of reading just the first few rows
displaying, Reading DB2 Execution Plans, The Underlying Process of Displaying Execution Plans–The Practical Process of Displaying Execution Plans, Getting the DB2 Execution Plan
interpreting, How to interpret the plan–Narrative interpretation of the execution plan
multipart, Complex Execution Plans
nonrobust, Nonrobust Execution Plans
robust, Robust Execution Plans–Narrative interpretation of the execution plan
tables for execution-plan data, Prerequisites

modifying queries with, Modifying the Query–Let DB2 know when to optimize the cost of reading just the first few rows

nested views created on the fly, Prevent too many outer joins from parsing at once

null conditions in indexed columns, Index Range-Condition Selectivity

optimization levels, Choosing the Optimization Level

type conversions with, Enabling Use of the Index You Want

updating statistics with, DB2 Optimization Prerequisites

db2exfmt tool, Reading DB2 Execution Plans

DBMS_STATS package, Oracle cost-based optimizer prerequisites

DELETE command, Complete Purge and Regrowth

deletes in index, performance effects of, Index Costs

denormalization, Glossary

in cyclic join graph cases, Case 2: Master-detail tables each hold copies of a foreign key that points to the same third table’s primary key, Cyclic join summary
when to use, Optimizing Queries with Distributed Filters

denormalized data, Glossary, Glossary

(see also normalized data)

detail join ratio, Nonunderlined numbers, Simplified Query Diagrams, Glossary

close to 1.0, affecting optimum execution plan, Detail Join Ratios Close to 1.0–Detail Join Ratios Close to 1.0

example of, Creating a Full Query Diagram

in query diagrams, Absolute table sizes (as opposed to relative sizes)

large, affecting optimum execution plan, Special Rules for Special Cases, Cases to Consider Hash Joins

less than 1.0

affecting optimum execution plan, Detail join ratios less than 1.0–Optimizing detail join ratios less than 1.0 with the rules
caused by query diagram with multiple roots, Query Diagrams with Multiple Roots

detail table, Glossary

developers, tuning their own SQL, Who Should Tune SQL?

diagrams (see query diagrams)

directed graph, Information Included in Query Diagrams, Information Included in Query Diagrams

(see also query diagrams)

disconnected query diagrams, Disconnected Query Diagrams–Disconnected Query Diagrams

distributed filters, Why Queries Sometimes Read Many Rows to Return Few–Optimizing Queries with Distributed Filters, Glossary

driving table, Nested-loops joins, Glossary

choosing, Standard Heuristic Join Order, Safe Cartesian Products–Safe Cartesian Products, Choosing the Driving Table–Choosing the Driving Table
forcing selection of, Execution-order hints

E

entity-relationship diagrams, Interpreting Query Diagrams

equality condition, index ranges with, Index Range-Condition Selectivity

errors (see problem cases)

EXCEPT operation (see set operations)

execution plans, Preface, Glossary

controlling

cost-based optimizer used for, Providing the Cost-Based Optimizer with Good Data
disabling undesired index, Preventing Use of the Wrong Indexes
disabling undesired join orders, Preventing Join Orders You Do Not Want–Preventing Join Orders You Do Not Want
enabling desired index, Enabling Use of the Index You Want–Enabling Use of the Index You Want
enabling desired join order, Enabling the Join Order You Want
forcing execution order with subqueries, Forcing Execution Order for Outer Queries and Subqueries–Forcing Execution Order for Outer Queries and Subqueries
techniques for, Universal Techniques for Controlling Plans
with DB2, Controlling Plans on DB2–Let DB2 know when to optimize the cost of reading just the first few rows
with Oracle cost-based optimizer, Controlling Plans on Oracle–Controlling the Choice of Oracle Optimizer, Controlling Oracle Cost-Based Execution Plans–Example
with Oracle rule-based optimizer, Controlling Plans on Oracle–Controlling Oracle Rule-Based Execution Plans
with SQL Server, Controlling Plans on SQL Server–Using FORCEPLAN

DB2

complex, Complex Execution Plans
displaying, Reading DB2 Execution Plans, The Underlying Process of Displaying Execution Plans–The Practical Process of Displaying Execution Plans, Getting the DB2 Execution Plan
interpreting, How to interpret the plan–Narrative interpretation of the execution plan
multipart, Complex Execution Plans
nonrubust, Nonrobust Execution Plans
robust, Robust Execution Plans–Narrative interpretation of the execution plan
tables for execution-plan data, Prerequisites

exercise for, Exercise (See Section A.2 for the solution to the exercise.), Chapter 6 Exercise Solution–Chapter 6 Exercise Solution

join order for

calculating cost of, Conceptual Demonstration of Query Diagrams in Use
choosing, Standard Heuristic Join Order–A Complex 17-Way Join, A Complex Example–A Complex Example, Choosing the Next Table to Join–When to Choose Early Joins to Upstream Nodes
controlling, Execution-order hints, A Complex 17-Way Join
for outer joins, Steps for Normal Outer Join Order Optimization–Example

optimum

assumptions for, Special Rules for Special Cases
finding, Deducing the Best Execution Plan, Standard Heuristic Join Order–A Complex 17-Way Join, A Complex Example–A Complex Example, Solving the Query Diagram
for Cartesian products, Special Rules for Special Cases, Safe Cartesian Products–Safe Cartesian Products
for detail join ratios close to 1.0, Detail Join Ratios Close to 1.0–Detail Join Ratios Close to 1.0
for detail join ratios less than 1.0, Detail join ratios less than 1.0–Optimizing detail join ratios less than 1.0 with the rules
for filter ratios close to each other, Close Filter Ratios–Close Filter Ratios
for join ratios less than 1.0, Join Ratios Less than 1.0–Master join ratios less than 1.0
for large detail join ratios, Special Rules for Special Cases, Cases to Consider Hash Joins
for large root table, Special Rules for Special Cases
for large rowcount returned by query, Cases to Consider Hash Joins
for master join ratios less than 1.0, Rules for join ratios less than 1.0, Master join ratios less than 1.0–Master join ratios less than 1.0
for master join ratios of 1.0, Special Rules for Special Cases
for one large table, A Special Case–Solving the Special Case Outside of Oracle
for one small filter ratio, Special Rules for Special Cases
for small rowcounts returned, Special Rules for Special Cases
robust execution plans as, Robust Execution Plans
special cases for, Special Rules for Special Cases

Oracle

complex, Complex Execution Plans
displaying, The Underlying Process of Displaying Execution Plans–The Practical Process of Displaying Execution Plans, Getting the Oracle Execution Plan
interpreting, How to interpret the plan–Narrative interpretation of the execution plan
multipart, Complex Execution Plans
nonrobust, Nonrobust Execution Plans
robust, Robust Execution Plans–Narrative interpretation of the execution plan
table for plan data, Prerequisites

parallel, Controlling Plans on Oracle, Controlling the Choice of Oracle Optimizer

robust, Robust Execution Plans–Robust Execution Plans, Glossary

creating missing indexes for, Missing Indexes
improving upon, Merged Join and Filter Indexes
reasons to choose, Summary

SQL Server

complex, Complex Execution Plans
displaying, Displaying Execution Plans–Displaying execution plans textually, Getting the SQL Server Execution Plan
interpreting, How to Interpret the Plan–Narrative Interpretation of the Execution Plan
nonrubust, Interpreting Nonrobust Execution Plans

tools for generating, Viewing and Interpreting Execution Plans, Reading DB2 Execution Plans

exercises

creating query diagrams, Exercises (See Section A.1 for the solution to each exercise.)–Exercises (See Section A.1 for the solution to each exercise.), Chapter 5 Exercise Solutions–Exercise 6
diagramming and tuning query with subqueries, Exercise (See Section A.3 for the solution to the exercise.), Chapter 7 Exercise Solution–Chapter 7 Exercise Solution
finding optimum execution plan, Exercise (See Section A.2 for the solution to the exercise.), Chapter 6 Exercise Solution–Chapter 6 Exercise Solution

EXISTS-type subquery, Diagramming EXISTS subqueries–Diagramming EXISTS subqueries, Glossary

execution order when using, Forcing Execution Order for Outer Queries and Subqueries
replacing INTERSECT, Queries with Set Operations

EXPLAIN PLAN FOR statement, The Underlying Process of Displaying Execution Plans, The Underlying Process of Displaying Execution Plans

EXPLAIN_ tables, Prerequisites

expressions, indexes disabled when using, Preventing Use of the Wrong Indexes

extents, Tables

F

filter conditions, Glossary, Glossary

(see also filters)

filter independence, Filter Selectivity, Glossary

filter ratio, Underlined numbers, Glossary

calculating, Compute filter and join ratios–Compute filter and join ratios
choosing driving table using, Standard Heuristic Join Order, Choosing the Driving Table
close to each other, affecting optimum execution plan, Close Filter Ratios–Close Filter Ratios
example of, Creating a Full Query Diagram
notation in query diagrams, Underlined numbers
of later tables, considering in join order, Accounting for Benefits from Later Joins
one small filter ratio, affecting optimum execution plan, Special Rules for Special Cases
precision of, Shortcuts, Simplified Query Diagrams
query-specific information provided by, Interpreting Query Diagrams
rowcount and, Simplified Query Diagrams
subquery adjusted filter ratio, Diagramming EXISTS subqueries, Diagramming EXISTS subqueries

filter redundancy, Filter Selectivity, Glossary

filtered outer joins, Filtered outer joins–Filtered outer joins

filters

absence of, in queries, Unfiltered Joins
distributed, Why Queries Sometimes Read Many Rows to Return Few–Optimizing Queries with Distributed Filters, Glossary
index merging filter column and join key, Merged Join and Filter Indexes–Merged Join and Filter Indexes
join filters, Simplified Query Diagrams, Rules for join ratios less than 1.0, One-to-one joins with hidden join filters in both directions, Glossary
not included in query diagrams, Filter condition details
notation for, Filtered outer joins
notation for best and lesser filters, Simplified Query Diagrams
percentage of table satisfying (see filter ratio)
places applied in database, Calculating Selectivity
post-read filter, Narrative interpretation of the execution plan, Narrative interpretation of the execution plan, Glossary
selectivity of, Filter Selectivity–Filter Selectivity
two-node filter, Case 3: Two-node filter (nonunique on both ends) between nodes is already linked through normal joins–Case 3: Two-node filter (nonunique on both ends) between nodes is already linked through normal joins, Cyclic join summary
uniqueness of, guaranteeing, Creating Query Diagrams
work required by database for, Calculating Selectivity

FIRST_ROWS hint, Approaches to tuning with hints

focus table, Glossary

fonts used in this book, Conventions Used in This Book

FORCEPLAN option, Using FORCEPLAN

foreign key, Glossary, Glossary

full index scan, Index Range-Condition Selectivity, Glossary

full query diagrams, Full Query Diagrams–When Query Diagrams Help the Most, Creating a Full Query Diagram–Creating a Full Query Diagram

full table scan, Full Table Scans, Glossary

forcing use of, Table-access hints, Modifying the Query
when to use, Choosing Between a Full Table Scan and Indexed Access–Choosing Between a Full Table Scan and Indexed Access

FULL() hint, Table-access hints

function-based indexes, Index Range-Condition Selectivity

functions, indexes disabled when using, Enabling Use of the Index You Want

G

graph theory, Cyclic Join Graphs

graphs, Cyclic Join Graphs, Cyclic Join Graphs

(see also cyclic join graphs; query diagrams)

GROUP BY clause (see aggregation; grouping)

grouping, not included in query diagram, Ordering and aggregation

H

hash bucket, Hash joins

HASH hint, Modifying the Query

hash joins, Hash joins, Glossary

forcing use of, Join-method hints, Modifying the Query
in Oracle execution plan, Nonrobust Execution Plans
in SQL Server execution plan, Interpreting Nonrobust Execution Plans
when to use, Conceptual Demonstration of Query Diagrams in Use, Completing the Solution for an Eight-Way Join, Cases to Consider Hash Joins–Cases to Consider Hash Joins, The Case for Nested Loops, Unfiltered Joins

HAVING clause (see post-group filtering)

hidden join filters, Simplified Query Diagrams, Rules for join ratios less than 1.0, One-to-one joins with hidden join filters in both directions

high-water mark, Tables, Complete Purge and Regrowth, Glossary

hints

in Oracle, General hint syntax–Example
in SQL Server, Modifying the Query–Hint Examples

hot blocks, Caching in the Database, Caching in the Database, Glossary

(see also self-caching)
caching of, Caching in the Database, Caching in the Database
choosing for performance, Caching in the Database
hot data grouped in, Caching in the Database
indexed access and, Choosing Between a Full Table Scan and Indexed Access
physical I/O on, Caching in the Database
query causing, Caching in the Database

I

I/O (Input/Output), Glossary

logical, Caching in the Database, Caching in the Database, Selectivity on Table Rows Reached from the Index, Glossary
physical, Caching in the Database, Caching in the Database, Glossary

icons used in this book, Conventions Used in This Book

IN condition, execution order when using, Forcing Execution Order for Outer Queries and Subqueries

index blocks, B-Tree Indexes, Glossary, Glossary, Glossary

index range scan, B-Tree Indexes, Glossary

index range-condition selectivity, Index Range-Condition Selectivity–Index Range-Condition Selectivity

INDEX() hint, Table-access hints

index-organized tables, Index-Organized Tables

indexed table access, Indexed Table Access–Indexed Table Access

in DB2 execution plan, How to interpret the plan
in Oracle execution plan, How to interpret the plan
in SQL Server execution plan, How to Interpret the Plan
when to use, Choosing Between a Full Table Scan and Indexed Access–Choosing Between a Full Table Scan and Indexed Access

indexes, Indexes–Index Costs, Indexed Table Access–Indexed Table Access, Glossary

adding for specific queries, Merged Join and Filter Indexes–Merged Join and Filter Indexes

adding, effects on performance, Index Costs

B-tree indexes, B-Tree Indexes, Glossary

bit-mapped indexes, Bit-Mapped Indexes

combining, Combining Indexes

date-type columns in, Index Range-Condition Selectivity

disabling use of, Preventing Use of the Wrong Indexes

dropping, risks of, Missing Indexes

enabling use of, Enabling Use of the Index You Want–Enabling Use of the Index You Want

forcing use in descending order, Table-access hints

forcing use of specific index, Table-access hints, Modifying the Query

full index scan, Index Range-Condition Selectivity, Glossary

function-based, Index Range-Condition Selectivity

with merged join key and filter columns, Merged Join and Filter Indexes–Merged Join and Filter Indexes

missing, Simple Examples, Completing the Solution for an Eight-Way Join, Missing Indexes

rowids used by, B-Tree Indexes

satisfying query without accessing database, Select lists

selectivity on rows reached from, Selectivity on Table Rows Reached from the Index–Selectivity on Table Rows Reached from the Index

size of

caching and, Caching in the Database, Caching in the Database
cost-based optimizer’s response to, Absolute table sizes (as opposed to relative sizes)

statistics for, used by cost-based optimizer, Providing the Cost-Based Optimizer with Good Data

updating, performance effects of, Index Costs

INDEX_DESC() hint, Table-access hints

individual-condition filter selectivity, Filter Selectivity, Glossary

inequality condition, index ranges with, Index Range-Condition Selectivity, Index Range-Condition Selectivity

inner joins, Inner joins, Place inner joins first in your FROM clause, Glossary

Input/Output (see I/O)

inserts into index, performance effects of, Index Costs

INTERSECT operation (see set operations)

italic font used in this book, Conventions Used in This Book, Conventions Used in This Book

J

join execution methods, Join Execution Methods–Join methods summary

hash joins, Hash joins

forcing use of, Join-method hints, Modifying the Query
in Oracle execution plan, Nonrobust Execution Plans
in SQL Server execution plan, Interpreting Nonrobust Execution Plans
when to use, Conceptual Demonstration of Query Diagrams in Use, Completing the Solution for an Eight-Way Join, Cases to Consider Hash Joins–Cases to Consider Hash Joins, The Case for Nested Loops, Unfiltered Joins

nested-loops joins, Nested-loops joins

in DB2 execution plan, How to interpret the plan
forcing use of, Join-method hints, Modifying the Query
in Oracle execution plan, How to interpret the plan
in SQL Server execution plan, How to Interpret the Plan
when to use, Robust Execution Plans, The Case for Nested Loops–The Case for Nested Loops

sort-merge joins, Sort-merge joins

in DB2 execution plan, Nonrobust Execution Plans
in Oracle execution plan, Nonrobust Execution Plans
when to use, Completing the Solution for an Eight-Way Join, Unfiltered Joins

join filters, Simplified Query Diagrams, Rules for join ratios less than 1.0, One-to-one joins with hidden join filters in both directions, Glossary

join key, merged with filter column in index, Merged Join and Filter Indexes–Merged Join and Filter Indexes

join order, Execution-order hints

(see also execution plans)
calculating cost of, Conceptual Demonstration of Query Diagrams in Use
choosing, Standard Heuristic Join Order–A Complex 17-Way Join, A Complex Example–A Complex Example, Choosing the Next Table to Join–When to Choose Early Joins to Upstream Nodes
controlling, Execution-order hints, A Complex 17-Way Join
disabling undesired join orders, Preventing Join Orders You Do Not Want–Preventing Join Orders You Do Not Want
enabling desired join order, Enabling the Join Order You Want
for outer joins, Steps for Normal Outer Join Order Optimization–Example

join ratio, Nonunderlined numbers, Creating a Full Query Diagram, Glossary

(see also detail join ratio; master join ratio)

calculating, Compute filter and join ratios–Compute filter and join ratios

information about actual data provided by, Interpreting Query Diagrams

less than 1.0, Join Ratios Less than 1.0–Master join ratios less than 1.0, Glossary

(see also join filters)

in query diagrams, Nonunderlined numbers, Simplified Query Diagrams

referential integrity, effect on, Shortcuts

shortcuts for, Shortcuts, Shortcuts

join skeleton (query skeleton), Glossary

joins, Joins–Outer joins, Glossary

anti-join, Diagramming NOT EXISTS subqueries, Glossary

Cartesian join, Join Types, Glossary

condition for, Glossary

missing, Query Diagrams with Multiple Roots, Case 1: Missing join conditions, Joins with No Primary Key
not included in query diagrams, Detailed join conditions

correlation join, Forcing Execution Order for Outer Queries and Subqueries, Diagramming Queries with Subqueries, Glossary

cyclic, Cyclic Join Graphs–Cyclic join summary, Glossary

forcing order of, Controlling Oracle Rule-Based Execution Plans, Modifying the Query

hash join (see hash joins)

inner joins, Inner joins, Place inner joins first in your FROM clause, Glossary

many-to-one, Glossary

missing, Disconnected Query Diagrams

multipart join, Case 4: Multipart join from two foreign keys is spread over two tables to a multipart primary key–Case 4: Multipart join from two foreign keys is spread over two tables to a multipart primary key

nested-loops join (see nested-loops joins)

one-to-many, unintended, Case 3: Root detail tables that are usually no more than one-to-one

one-to-one, Case 1: Two one-to-one master tables share the same detail table–Case 1: Two one-to-one master tables share the same detail table, One-to-One Joins–Conventions to display one-to-one joins

order of (see join order)

outer joins (see outer joins)

in query diagrams, Links, Diagram joins to the first focus–Change focus and repeat

redundant joins, Missing redundant join conditions

semi-join, Diagramming EXISTS subqueries–Diagramming EXISTS subqueries, Glossary

sort-merge join (see sort-merge joins)

unfiltered, Unfiltered Joins

unnecessary, in queries with views, Unnecessary nodes and joins

without primary key, Joins with No Primary Key

K

keys

foreign key, Glossary, Glossary
join key, merged with filter columns in index, Merged Join and Filter Indexes–Merged Join and Filter Indexes
primary key, Links, Joins with No Primary Key, Glossary

L

LEADING hint, Execution-order hints

leaf blocks, B-Tree Indexes, Glossary

least recently used blocks (see LRU blocks)

least recently used caching (see LRU caching)

links in query diagrams, Links

logic errors, A Bonus, A Bonus

(see also problem cases)

logical I/O, Glossary

performance of, Caching in the Database, Selectivity on Table Rows Reached from the Index
pure, caching used for, Caching in the Database

LOOP hint, Modifying the Query

LRU (least recently used) blocks, Caching in the Database

LRU (least recently used) caching, Caching in the Database, Glossary

M

many-to-one join, Glossary

master join ratio, Nonunderlined numbers, Glossary

equal to 1.0, affecting optimum execution plan, Special Rules for Special Cases
example of, Creating a Full Query Diagram
less than 1.0, affecting optimum execution plan, Rules for join ratios less than 1.0, Master join ratios less than 1.0–Master join ratios less than 1.0
shortcuts for, Shortcuts

master table, Glossary

Mastering Oracle SQL (Mishra; Beaulieu), Objectives of This Book

memory-only data access (see pure logical I/O)

merge joins (see sort-merge joins)

merged join and filter indexes, Merged Join and Filter Indexes–Merged Join and Filter Indexes

Microsoft SQL Server (see SQL Server)

middleware, Why Tune SQL?, Middleware Processes Handling Too Many Rows, Glossary

Millsap, Cary (Optimizing Oracle Performance), Missing Indexes

MINUS operation (see set operations)

Mishra, Sanjay (Mastering Oracle SQL), Objectives of This Book

most recently used blocks (see MRU blocks)

MRU (most recently used) blocks, Caching in the Database

multipart execution plans, Complex Execution Plans

multipart joins, Case 4: Multipart join from two foreign keys is spread over two tables to a multipart primary key–Case 4: Multipart join from two foreign keys is spread over two tables to a multipart primary key

multiple-condition filter selectivity, Filter Selectivity, Glossary

multitable clusters, Multitable Clusters

multitable queries

join execution methods for, Join Execution Methods–Join methods summary
joins for, Joins–Outer joins

multitable queries, Glossary

(see also complex query)

N

nested-loops joins, Nested-loops joins, Glossary

in DB2 execution plan, How to interpret the plan
forcing use of, Join-method hints, Modifying the Query, Modifying the Query
in Oracle execution plan, How to interpret the plan
in SQL Server execution plan, How to Interpret the Plan
when to use, Robust Execution Plans, The Case for Nested Loops–The Case for Nested Loops

nodes

cloud of, Standard Heuristic Join Order
in query diagrams, Nodes
unnecessary, in queries with views, Unnecessary nodes and joins

nonunderlined numbers in query diagrams, Nonunderlined numbers

nonunique indexes, performance effects of, Index Costs

normal form, query diagrams matching, Interpreting Query Diagrams

normalized data, When Query Diagrams Help the Most, Glossary, Glossary

(see also denormalized data)

NOT EXISTS-type subquery, Diagramming NOT EXISTS subqueries–Diagramming NOT EXISTS subqueries, Queries with Set Operations, Glossary

null values

database interpretation of, Filtered outer joins
in indexes, Index Range-Condition Selectivity, Index Range-Condition Selectivity, Modifying the Query

numbers in query diagrams, Underlined numbers, Nonunderlined numbers, Nonunderlined numbers

(see also filter ratio; join ratio)

NVL() function, Preventing Use of the Wrong Indexes

O

one-to-many joins, unintended, Case 3: Root detail tables that are usually no more than one-to-one

one-to-one joins, Case 1: Two one-to-one master tables share the same detail table–Case 1: Two one-to-one master tables share the same detail table, One-to-One Joins–Conventions to display one-to-one joins

online queries, large, Large Online Queries–Large Online Queries

optimization levels in DB2, Choosing the Optimization Level

OPTIMIZE FOR ROWS clause, Let DB2 know when to optimize the cost of reading just the first few rows

optimizers (see cost-based optimizer; rule-based optimizer)

Optimizing Oracle Performance (Millsap), Missing Indexes

OPTION(FAST 1) hint, Modifying the Query

OPTION(FORCE ORDER) hint, Modifying the Query

OPTION(LOOP JOIN) hint, Modifying the Query

OR operator, indexes disabled when using, Enabling Use of the Index You Want

Oracle

books about, Objectives of This Book, Missing Indexes

cost-based optimizer, Controlling Plans on Oracle, Controlling Plans on Oracle, Controlling Oracle Cost-Based Execution Plans–Example

decoupling index reads from table reads, The Oracle Solution–The Oracle Solution

execution plans

complex, Complex Execution Plans
displaying, The Underlying Process of Displaying Execution Plans–The Practical Process of Displaying Execution Plans, Getting the Oracle Execution Plan
interpreting, How to interpret the plan–Narrative interpretation of the execution plan
multipart, Complex Execution Plans
nonrobust, Nonrobust Execution Plans
robust, Robust Execution Plans–Narrative interpretation of the execution plan
table for plan data, Prerequisites

extents, Tables

function-based indexes, Index Range-Condition Selectivity

hints used to control queries, General hint syntax–Example

index-organized tables, Index-Organized Tables

MINUS for EXCEPT operation, Queries with Set Operations

notation for filter conditions in joins, Filtered outer joins

null conditions in indexed columns, Index Range-Condition Selectivity

NVL() function used to disable indexes, Preventing Use of the Wrong Indexes

optimizers, choosing between, Controlling the Choice of Oracle Optimizer–Controlling the Choice of Oracle Optimizer

partitioned tables, Partitioned Tables

rule-based optimizer, Providing the Cost-Based Optimizer with Good Data, Controlling Plans on Oracle–Controlling Plans on Oracle

TRUNCATE command, Complete Purge and Regrowth

type conversions with, Index Range-Condition Selectivity, Enabling Use of the Index You Want

ORDER BY clause (see ordering)

ORDERED hint, Execution-order hints

ordering, not included in query diagram, Ordering and aggregation

Ouroboros pattern (see purge eldest pattern)

outer joins, Outer joins, Outer Joins–Outer Joins, Glossary

abnormal, Outer Joins–Outer joins to a detail table with a filter
to detail table, Outer joins pointing toward the detail table
filtered, Filtered outer joins–Filtered outer joins
to filtered detail table, Outer joins to a detail table with a filter
improving performance of, in DB2, Prevent too many outer joins from parsing at once
join orders disabled when using, Outer joins
leading to inner joins, Outer joins leading to inner joins
necessity of ratios for, Shortcuts
notation for, Change focus and repeat
optimizing join order for, Steps for Normal Outer Join Order Optimization–Example
to views, Outer joins to views–Outer joins to views
when to use, Outer Joins

O’Reilly & Associates, Inc., contact information for, Comments and Questions

P

parallel execution plans, Controlling Plans on Oracle, Controlling the Choice of Oracle Optimizer

parentheses (())

in FROM clause for DB2, Prevent too many outer joins from parsing at once
surrounding ordered list of items, Conventions Used in This Book

partitioned tables, Partitioned Tables

path to data (see data access; execution plans)

performance, Why Tune SQL?

(see also SQL tuning)
adding indexes affecting, Index Costs
caching scheme affecting, Caching in the Database
calculations performed on data affecting, Why Tune SQL?
compared to throughput, Why Tune SQL?
database operations affecting, Why Tune SQL?
logical I/Os and, Selectivity on Table Rows Reached from the Index
physical layout of table rows and, Tables
SQL as factor in, Preface

physical I/O, Glossary

caching used to minimize, Caching in the Database
performance of, Caching in the Database

PLAN_TABLE table, Prerequisites

plus sign (+)

in comment, General hint syntax
in join clause, Filtered outer joins

post-group filtering, not included in query diagram, Ordering and aggregation

post-read filter, Narrative interpretation of the execution plan, Narrative interpretation of the execution plan, Glossary

primary keys, Glossary

indicated by arrows in query diagrams, Links
missing from join, Joins with No Primary Key

problem cases, Outside-the-Box Solutions

(see also exercises; SQL tuning)
abnormal outer joins, Outer Joins–Outer joins to a detail table with a filter
all tables relatively small except one, A Special Case–Solving the Special Case Outside of Oracle
Cartesian products between first set of rows, Safe Cartesian Products–Safe Cartesian Products
corner-case problems, A Bonus, Interpreting Query Diagrams, Queries with Views, Outer joins to views
cyclic join graphs, Cyclic Join Graphs–Cyclic join summary
detail join ratios close to 1.0, Detail Join Ratios Close to 1.0–Detail Join Ratios Close to 1.0
detail join ratios less than 1.0, Detail join ratios less than 1.0–Optimizing detail join ratios less than 1.0 with the rules, Query Diagrams with Multiple Roots
disconnected query diagrams, Disconnected Query Diagrams–Disconnected Query Diagrams
filter ratios close to each other, Close Filter Ratios–Close Filter Ratios
join ratios less than 1.0, Join Ratios Less than 1.0–Master join ratios less than 1.0
joining to the same table twice, Case 1: Two one-to-one master tables share the same detail table
joins with no primary key, Joins with No Primary Key
large detail join ratios, Cases to Consider Hash Joins
large rowcount returned by query, Cases to Consider Hash Joins
master join ratios less than 1.0, Rules for join ratios less than 1.0, Master join ratios less than 1.0–Master join ratios less than 1.0
middleware processes handling too many rows, Middleware Processes Handling Too Many Rows
one-to-one joins, Case 1: Two one-to-one master tables share the same detail table–Case 1: Two one-to-one master tables share the same detail table, One-to-One Joins–Conventions to display one-to-one joins
outer joins to views, Outer joins to views–Outer joins to views
performance and throughput problems, Why Tune SQL?
queries aggregating too many rows, Unsolvable Problems, Aggregations of Many Details
queries repeated a large number of times, Unsolvable Problems, When Very Fast Is Not Fast Enough–Merging Repeated Queries into a Preexisting Query
queries returning too many rows, Unsolvable Problems, Queries that Return Data from Too Many Rows–Solutions
queries running slowly even when returning few rows, Unsolvable Problems, Tuned Queries that Return Few Rows, Slowly–Optimizing Queries with Distributed Filters
queries with set operations, Queries with Set Operations
queries with subqueries, Queries with Subqueries, Tuning Queries with Subqueries–Tuning Queries with Subqueries
queries with views, Queries with Views, Tuning Queries with Views–Unnecessary nodes and joins
query diagrams with multiple roots, Query Diagrams with Multiple Roots–Case 4: Converting an existence check to an explicit subquery
redundant reads in view-using queries, Redundant reads in view-using queries
unfiltered joins, Unfiltered Joins
unnecessary nodes and joins, Unnecessary nodes and joins

programmers, tuning their own SQL, Who Should Tune SQL?

pure logical I/O, caching used for, Caching in the Database

purge eldest pattern, Purge Eldest

purge, not by age pattern, Purge, Not by Age

PUSH_SUBQ hint, Execution-order hints

Q

queries, Choosing Between a Full Table Scan and Indexed Access

(see also joins)

aggregating too many rows, Unsolvable Problems, Aggregations of Many Details

altering to enable optimum execution plan, Altering the SQL to Enable the Best Plan

for batch reports, large, Large Batch Reports–Solutions

blind, Large Online Queries

choosing indexed access or full table scan for, Choosing Between a Full Table Scan and Indexed Access

complex queries, Diagramming and Tuning Complex SQL Queries

abnormal outer joins in, Outer Joins–Outer joins to a detail table with a filter
cyclic joins in, Cyclic Join Graphs–Cyclic join summary
disconnected, Disconnected Query Diagrams–Disconnected Query Diagrams
joins with no primary keys in, Joins with No Primary Key
multiple roots in, Query Diagrams with Multiple Roots–Case 4: Converting an existence check to an explicit subquery
one-to-one joins in, One-to-One Joins–Conventions to display one-to-one joins
set operations in, Queries with Set Operations
subqueries in, Queries with Subqueries
views in, Queries with Views–Unnecessary nodes and joins

distributed filters in, Why Queries Sometimes Read Many Rows to Return Few–Optimizing Queries with Distributed Filters

errors in (see problem cases)

middleware processes handling too many rows in, Middleware Processes Handling Too Many Rows

modifying with DB2, Modifying the Query–Let DB2 know when to optimize the cost of reading just the first few rows

modifying with Oracle, General hint syntax–Example

modifying with SQL Server, Modifying the Query–Hint Examples

multitable queries, Joins–Join methods summary

online, large, Large Online Queries–Large Online Queries

performance effects of, estimating, Missing Indexes

read locks in, disabling, Modifying the Query

repeated a large number of times, Unsolvable Problems, When Very Fast Is Not Fast Enough–Merging Repeated Queries into a Preexisting Query

returning too many rows, Unsolvable Problems, Queries that Return Data from Too Many Rows–Solutions

running slowly even when returning few rows, Unsolvable Problems, Tuned Queries that Return Few Rows, Slowly–Optimizing Queries with Distributed Filters

set operations in, Queries with Set Operations

simple, Glossary

creating query diagrams for, Creating Query Diagrams–Shortcuts
criteria for, Diagramming and Tuning Complex SQL Queries
full query diagrams for, Full Query Diagrams–When Query Diagrams Help the Most
interpreting query diagrams for, Interpreting Query Diagrams–Interpreting Query Diagrams
simplified query diagrams for, Simplified Query Diagrams–Simplified Query Diagrams

single-table queries, Single-Table Access Paths–Choosing Between a Full Table Scan and Indexed Access

subqueries in, Queries with Subqueries

converting existence checks to, Case 4: Converting an existence check to an explicit subquery
correlated, forcing evaluation of, Execution-order hints
DB2 execution plan for, Complex Execution Plans
exercise for, Exercise (See Section A.3 for the solution to the exercise.), Chapter 7 Exercise Solution–Chapter 7 Exercise Solution
EXISTS subqueries, Forcing Execution Order for Outer Queries and Subqueries, Diagramming EXISTS subqueries–Diagramming EXISTS subqueries, Queries with Set Operations, Glossary
forcing execution order when using, Forcing Execution Order for Outer Queries and Subqueries–Forcing Execution Order for Outer Queries and Subqueries
NOT EXISTS subqueries, Diagramming NOT EXISTS subqueries–Diagramming NOT EXISTS subqueries, Queries with Set Operations, Glossary
Oracle execution plan for, Complex Execution Plans
query diagrams for, Diagramming Queries with Subqueries–Diagramming NOT EXISTS subqueries
SQL Server execution plan for, Complex Execution Plans
tuning, Tuning Queries with Subqueries–Tuning Queries with Subqueries

view-defining, Queries with Views, Glossary

view-using, Queries with Views, Diagramming View-Using Queries–Diagramming View-Using Queries, Redundant reads in view-using queries, Glossary

views in, Queries with Views–Unnecessary nodes and joins

Query Analyzer (see SQL Server Query Analyzer)

query diagrams, A Bonus, Glossary

abstractness of, Table names
compared to entity-relationship diagrams, Interpreting Query Diagrams
correlation joins in, Diagramming Queries with Subqueries
creating, Creating Query Diagrams–Shortcuts, Reducing the Query to a Query Diagram–Creating a Full Query Diagram
cyclic joins in, Cyclic Join Graphs–Cyclic join summary, Glossary
disconnected, Disconnected Query Diagrams–Disconnected Query Diagrams
example usage of, Conceptual Demonstration of Query Diagrams in Use
exercises for, Exercises (See Section A.1 for the solution to each exercise.)–Exercises (See Section A.1 for the solution to each exercise.), Chapter 5 Exercise Solutions–Exercise 6
filter conditions not included in, Filter condition details
filter ratios in, Underlined numbers, Compute filter and join ratios–Compute filter and join ratios, Shortcuts, Interpreting Query Diagrams, Simplified Query Diagrams
full, Full Query Diagrams–When Query Diagrams Help the Most, Creating a Full Query Diagram–Creating a Full Query Diagram
grouping not included in, Ordering and aggregation
interpreting, Interpreting Query Diagrams–Interpreting Query Diagrams
join conditions not included in, Detailed join conditions
join ratios in, Nonunderlined numbers, Absolute table sizes (as opposed to relative sizes), Compute filter and join ratios–Compute filter and join ratios, Shortcuts, Interpreting Query Diagrams, Simplified Query Diagrams
joins in, Links, Diagram joins to the first focus–Change focus and repeat
links in, Links
multiple roots in, Query Diagrams with Multiple Roots–Case 4: Converting an existence check to an explicit subquery
nodes in, Nodes
nonunderlined numbers in, Nonunderlined numbers
normal form reflected in, Interpreting Query Diagrams
one-to-one joins in, Conventions to display one-to-one joins
ordering not included in, Ordering and aggregation
outer joins in, Change focus and repeat
post-group filtering not included in, Ordering and aggregation
reasons for using, Why a New Method?
select lists not included in, Select lists
simplified, Why a New Method?, Simplified Query Diagrams–Simplified Query Diagrams, Creating a Simplified Query Diagram
subqueries in, Diagramming Queries with Subqueries–Diagramming NOT EXISTS subqueries
table aliases in, Nodes
table names not included in, Table names
underlined numbers in, Underlined numbers
view-using queries in, Diagramming View-Using Queries–Diagramming View-Using Queries
when to use, When Query Diagrams Help the Most

query skeleton, Glossary, Glossary

questions about this book, sending, Comments and Questions

R

range-condition selectivity for index (see index range-condition selectivity)

ratios (see benefit-to-cost ratio; cache-hit ratio; correlation preference ratio; filter ratio; join ratio)

RBO (see rule-based optimizer)

read locks, disabling in queries, Modifying the Query

redundant joins, enabling join order, Missing redundant join conditions

referential integrity, Shortcuts, Glossary

relative running rowcount, Steps for Normal Outer Join Order Optimization

repeated queries, Unsolvable Problems, When Very Fast Is Not Fast Enough–Merging Repeated Queries into a Preexisting Query

reports, batch, Large Batch Reports–Solutions

robust execution plans, Robust Execution Plans–Robust Execution Plans, Glossary

creating missing indexes for, Missing Indexes
improving upon, Merged Join and Filter Indexes
reasons to choose, Summary

root block, B-Tree Indexes, Glossary

root detail table, Glossary

root nodes, multiple, in query diagram, Query Diagrams with Multiple Roots–Case 4: Converting an existence check to an explicit subquery

rowcount, Glossary

correlated subqueries affecting, Tuning Queries with Subqueries

estimating cost of joins using, Conceptual Demonstration of Query Diagrams in Use

filter ratios and, Simplified Query Diagrams

inner joins affecting, Join Order for an Eight-Way Join

large, join execution method for, Cases to Consider Hash Joins

relative running rowcount, Steps for Normal Outer Join Order Optimization

running rowcount, Outer Joins

small

join execution method for, Special Rules for Special Cases
running slowly, Why Queries Sometimes Read Many Rows to Return Few–Optimizing Queries with Distributed Filters

rowid, B-Tree Indexes, Glossary

rows, reading first rows quickly, Let DB2 know when to optimize the cost of reading just the first few rows, Modifying the Query

rowset, Glossary

RULE hint, Robust Execution Plans

rule-based optimizer, Providing the Cost-Based Optimizer with Good Data, Controlling Plans on Oracle–Controlling Plans on Oracle

choosing, Controlling the Choice of Oracle Optimizer–Controlling the Choice of Oracle Optimizer
cost-based optimizer used with, Controlling the Choice of Oracle Optimizer
disabling unintentionally, Controlling the Choice of Oracle Optimizer

running rowcount, Outer Joins

S

select lists, not included in query diagrams, Select lists

selectivity, Calculating Selectivity

index range-condition selectivity, Index Range-Condition Selectivity–Index Range-Condition Selectivity
of filters, Filter Selectivity–Filter Selectivity
on table rows reached from index, Selectivity on Table Rows Reached from the Index–Selectivity on Table Rows Reached from the Index

self-caching, Caching in the Database, Glossary

semi-join, Diagramming EXISTS subqueries–Diagramming EXISTS subqueries, Glossary

set operations, queries using, Queries with Set Operations

simple queries, Glossary

creating query diagrams for, Creating Query Diagrams–Shortcuts
criteria for, Diagramming and Tuning Complex SQL Queries
full query diagrams for, Full Query Diagrams–When Query Diagrams Help the Most
interpreting query diagrams for, Interpreting Query Diagrams–Interpreting Query Diagrams
simplified query diagrams for, Simplified Query Diagrams–Simplified Query Diagrams

simplified query diagrams, Why a New Method?, Simplified Query Diagrams–Simplified Query Diagrams, Creating a Simplified Query Diagram

single-table access paths, Single-Table Access Paths–Choosing Between a Full Table Scan and Indexed Access

single-table clusters, Single-Table Clusters

single-table queries, Single-Table Access Paths–Choosing Between a Full Table Scan and Indexed Access

software, Glossary

(see also application; middleware)

software tools for generating execution plans, Viewing and Interpreting Execution Plans, Reading DB2 Execution Plans

sort-merge joins, Sort-merge joins, Glossary

in DB2 execution plan, Nonrobust Execution Plans
in Oracle execution plan, Nonrobust Execution Plans
when to use, Completing the Solution for an Eight-Way Join, Unfiltered Joins

SQL Server

COALESCE() function, Preventing Use of the Wrong Indexes

execution plans

complex, Complex Execution Plans
controlling, Controlling Plans on SQL Server–Using FORCEPLAN
displaying, Displaying Execution Plans–Displaying execution plans textually, Getting the SQL Server Execution Plan
interpreting, How to Interpret the Plan–Narrative Interpretation of the Execution Plan
nonrobust, Interpreting Nonrobust Execution Plans

FORCEPLAN option, Using FORCEPLAN

modifying queries with, Modifying the Query–Hint Examples

type conversions with, Enabling Use of the Index You Want

SQL Server Query Analyzer, Displaying Execution Plans, Displaying execution plans graphically, Displaying execution plans textually

example of using, Getting the SQL Server Execution Plan
updating statistics in, SQL Server Optimization Prerequisites

SQL statements (see queries)

SQL tuning, Outside-the-Box Solutions

(see also execution plans; problem cases)

example of process, The Full Process, End to End

altering application as necessary, Altering the Application
altering database as necessary, Altering the Database to Enable the Best Plan
altering query as necessary, Altering the SQL to Enable the Best Plan
checking existing execution plans, Checking the Execution Plans–Getting the SQL Server Execution Plan
creating query diagram, Reducing the Query to a Query Diagram–Creating a Full Query Diagram
finding best execution plan, Solving the Query Diagram

improvement percentage requirements, Choosing Between a Full Table Scan and Indexed Access

reasons for, Why Tune SQL?

steps for, How This Book Can Help

types of solutions for, Objectives of This Book

who should perform, Audience for This Book, Who Should Tune SQL?

SQL, book about, Objectives of This Book, Objectives of This Book

(see also queries)

statistics

providing for cost-based optimizer, Providing the Cost-Based Optimizer with Good Data
updating for Oracle, Oracle cost-based optimizer prerequisites
updating with DB2, DB2 Optimization Prerequisites
updating with SQL Server, SQL Server Optimization Prerequisites

subqueries, Queries with Subqueries

converting existence checks to, Case 4: Converting an existence check to an explicit subquery
correlated, forcing evaluation of, Execution-order hints
DB2 execution plan for, Complex Execution Plans
exercise for, Exercise (See Section A.3 for the solution to the exercise.), Chapter 7 Exercise Solution–Chapter 7 Exercise Solution
EXISTS subqueries, Forcing Execution Order for Outer Queries and Subqueries, Diagramming EXISTS subqueries–Diagramming EXISTS subqueries, Queries with Set Operations, Glossary
forcing execution order when using, Forcing Execution Order for Outer Queries and Subqueries–Forcing Execution Order for Outer Queries and Subqueries
NOT EXISTS subqueries, Diagramming NOT EXISTS subqueries–Diagramming NOT EXISTS subqueries, Queries with Set Operations, Glossary
Oracle execution plan for, Complex Execution Plans
query diagrams for, Diagramming Queries with Subqueries–Diagramming NOT EXISTS subqueries
SQL Server execution plan for, Complex Execution Plans
tuning, Tuning Queries with Subqueries–Tuning Queries with Subqueries

subquery adjusted filter ratio, Diagramming EXISTS subqueries, Diagramming EXISTS subqueries, Glossary

subquery root detail table, Glossary

Sybase, Reading SQL Server Execution Plans

(see also SQL Server)
execution plans for, Reading SQL Server Execution Plans
FORCEPLAN option, Using FORCEPLAN

T

tables, Tables–Complete Purge and Regrowth

adding, for joins with hidden join filters, One-to-one joins with hidden join filters in both directions

aliases of, in query diagrams, Nodes

apples-and-oranges tables, Glossary

combining

for exact one-to-one joins, Exact one-to-one joins
for tables with hidden join filters, One-to-one joins with hidden join filters in both directions

complete purge and regrowth pattern for, Complete Purge and Regrowth

conditions on (see filters; joins; selectivity)

continuous growth pattern for, Continuous Growth

detail table, Glossary

driving, Nested-loops joins, Execution-order hints, Standard Heuristic Join Order, Safe Cartesian Products–Safe Cartesian Products, Choosing the Driving Table–Choosing the Driving Table, Glossary

focus table, Glossary

full table scan, Full Table Scans, Choosing Between a Full Table Scan and Indexed Access–Choosing Between a Full Table Scan and Indexed Access, Table-access hints, Modifying the Query, Glossary

growth patterns for, Tables–Complete Purge and Regrowth

high-water mark of, Tables, Complete Purge and Regrowth, Glossary

index-organized, Index-Organized Tables

indexed access of, Indexed Table Access–Choosing Between a Full Table Scan and Indexed Access, How to interpret the plan, How to interpret the plan, How to Interpret the Plan

large

affecting caching, Caching in the Database, Full Table Scans
affecting indexed read performance, Choosing Between a Full Table Scan and Indexed Access, Choosing Between a Full Table Scan and Indexed Access
affecting optimum execution plan, A Special Case–Solving the Special Case Outside of Oracle, Special Rules for Special Cases
nested loops for, The Case for Nested Loops

master table, Glossary

multitable clusters, Multitable Clusters

multitable queries, Joins–Join methods summary

names of, not included in query diagram, Table names

nodes representing (see nodes)

partitioned, Partitioned Tables

physical layout of, affecting performance, Tables

purge eldest pattern for, Purge Eldest

purge, not by age pattern for, Purge, Not by Age

root detail table, Glossary

selectivity on rows reached from index, Selectivity on Table Rows Reached from the Index–Selectivity on Table Rows Reached from the Index

separating, for one-to-one join to small subset, One-to-one join to a much smaller subset

single-table access paths, Single-Table Access Paths–Choosing Between a Full Table Scan and Indexed Access

single-table clusters, Single-Table Clusters

single-table queries, Single-Table Access Paths–Choosing Between a Full Table Scan and Indexed Access

size of

cost-based optimizer’s response to, Absolute table sizes (as opposed to relative sizes)
indicating necessity of index, Indexed Table Access
represented in query diagrams, Absolute table sizes (as opposed to relative sizes)

small, affecting caching, Caching in the Database

statistics for, used by cost-based optimizer, Providing the Cost-Based Optimizer with Good Data

throughput, Why Tune SQL?, Why Tune SQL?

(see also performance)

TOAD software tool, Viewing and Interpreting Execution Plans

transitivity, implying missing join conditions with, Case 1: Two one-to-one master tables share the same detail table

TRUNCATE command, Complete Purge and Regrowth

tuning (see SQL tuning)

tuple, Glossary

two-node filter, Case 3: Two-node filter (nonunique on both ends) between nodes is already linked through normal joins–Case 3: Two-node filter (nonunique on both ends) between nodes is already linked through normal joins, Cyclic join summary

type conversions, indexes enabled and disabled by, Index Range-Condition Selectivity, Enabling Use of the Index You Want

U

underlined numbers in query diagrams, Underlined numbers
unfiltered joins, Unfiltered Joins
UNION ALL operation (see set operations)
UNION operation (see set operations)
unique indexes, performance effects of, Index Costs
unique keys (see primary keys)
updates in index, performance effects of, Index Costs
uppercase text used in this book, Conventions Used in This Book
upstream nodes, when to choose, When to Choose Early Joins to Upstream Nodes
USE_HASH hint, Join-method hints
USE_NL hint, Join-method hints