Skip to Content

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
  • Insert..into
  • Insert..location
  • Insert..values
  • Load..into
  • 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:

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00801.1600/doc/pdf/iqrefso.pdf

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.
Tags: