Query Plan Changes- IQ 16
Query html plans are generated for load execution plans. Load execution html plans are generated for these statements:
- Alter table add column (with default value)
- Create Index
- Declare cursor for update
- Parallel IQ statements
To Generate html plans for load execution, you will need to set appropriate Query options:
- set option public.query_plan='on'
- set option public.query_detail='on'
- set option public.query_plan_as_html='on'
- set option public.query_plan_after_run='on'
- set option public.query_timing='on'
- set option public.query_plan_min_time='on'
- set option public.query_plan_as_html_directory='on'
For detailed information on these options:
These option can be set as temporary option as well, Html plans should be only used to evaluate the performance of a particular query or load. Query_plan database option shouldn't be set to ON as it can significantly impact performance, especially as the volume of insert..value statement increases.
Query Plan Nodes:
- Root Node: This node is created at very top of the Data Flow Tree.
- Sequencer: In the Data Flow Tree, this node has two or more childen. Pass1 branch of load has Child1 and Pass2 branch of load has Child2.
- Parallel Combiner: This Node takes multiple streams of rows from below, and 1 stream of rows is produced.
- Index Insert: Insert into indexes. Sorted data values used for HG or WD or TEXT are retrieved and inserted into index. Only unique constraint is applied during index insert.
- RowID Generator: Unique rowid for each row is generated.
- Leaf: This node is created at the bottom of the Data Flow Tree.
New Query Plan Nodes:
- Distinct Sort(inserter): Node in Data Flow Tree indicating early aggregation algorithm being used.
- Distinct Sort(retriever): Node in Data Flow Tree indicating early aggregation algorithm being used.
- Grouping Sort(inserter): Node in Data Flow Tree indicating early aggregaton algorithm being used.
- Grouping Sort(retriever): Node in Data Flow Tree indicating early aggregation algorithm being used.
- Join(Asymmetric Sort-Merge): Node in Data Flow Tree indicating asymmetric sort-merge join being used.
- Join(Asymmetric Sort-Merge Pushdown): Node in Data Flow Tree indicating asymmetric sort-merge join being used.
- Order By(Per-Work-Unit): Node in Data Flow Tree indicating an operator that performs a sort on a work-unit worth of data. For example, it displays on the left branch under the Asymmetric Sort-merge Join node.