Neoview Query Guide (R2.3, R2.4, R2.5)

Table of Contents
About This Document.........................................................................................................5
Supported Release Version Updates (RVUs)..........................................................................................5
Intended Audience.................................................................................................................................5
New and Changed Information in This Edition.....................................................................................5
Document Organization.........................................................................................................................5
Notation Conventions.............................................................................................................................5
General Syntax Notation...................................................................................................................5
Related Documentation..........................................................................................................................7
Neoview Customer Library...............................................................................................................7
Publishing History..................................................................................................................................9
HP Encourages Your Comments............................................................................................................9
1 Using Explain...............................................................................................................11
Generating Query Plans........................................................................................................................11
Neoview Performance Analyzer.....................................................................................................11
Explain Statement............................................................................................................................11
Explain Function..............................................................................................................................11
Capturing Query Plans for Review......................................................................................................12
Capturing Query Plans....................................................................................................................12
Historical Explain Database.......................................................................................................12
Capturing Query Plan Example.................................................................................................13
Querying the Explain Table.............................................................................................................14
2 Query Plan Operators.................................................................................................17
Operators and Description Fields.........................................................................................................18
Operators Used for Parallelism or as Process Boundaries..............................................................18
DAM and ESP Parallelism..........................................................................................................20
ROOT Operator..........................................................................................................................20
ESP_EXCHANGE Operator.......................................................................................................21
Repartitioning Rows ............................................................................................................22
PARTITION_ACCESS Operator................................................................................................22
SPLIT_TOP Operator.................................................................................................................23
Understanding Joins........................................................................................................................24
Nested Joins................................................................................................................................24
NESTED_JOIN Operator......................................................................................................25
NESTED_SEMI_JOIN Operator............................................................................................25
NESTED_ANTI_SEMI_JOIN Operator................................................................................26
LEFT_NESTED_JOIN Operator............................................................................................26
TUPLE_FLOW Operator.......................................................................................................27
Merge Joins.................................................................................................................................27
MERGE_JOIN Operator........................................................................................................27
MERGE_SEMI_JOIN Operator.............................................................................................28
MERGE_ANTI_SEMI_JOIN Operator..................................................................................28
LEFT_MERGE_JOIN Operator.............................................................................................29
Hybrid Hash Joins......................................................................................................................29
HYBRID_HASH_JOIN Operator..........................................................................................29
HYBRID_HASH_SEMI_JOIN Operator...............................................................................30
HYBRID_HASH_ANTI_SEMI_JOIN Operator....................................................................30
LEFT_HYBRID_HASH_JOIN Operator...............................................................................31
Table of Contents 3