Remotely assisted instructional learning (
RAIL)
Instructor-led training (
ILT)
Onsite dedicated training (
OST)
Price
USD $3,000
CAD $3,600
Course overview
This 5-day lecture course analyzes a variety of SQL/MX query plans and their features, such as the data flow architecture, process boundaries, query plan parallelism, and executor functionality in DP2. Also, it describes how row sets, compound statements, referential integrity constraints and triggers are implemented in a query plan. Learn to display and analyze query plans and the methods for forcing (changing) query plans.
Describe and use tools to analyze a variety of SQL/MX query plans
Describe the methods to influence or modify SQL/MX query plans with Control Query Default and Control Query Shape statements
Describe some of the opportunities for tuning SQL/MX queries in the following areas: query design, application design, and database design
Describe the SQL/MX query plan fundamentals, such as query compilation phases, query tree operators functions, data flow architecture, process boundaries, and parallel execution of a query plan
Benefits to you
Analyzing a variety of SQL/MX query plans: the operators, data flow, and parallelism
Modifying query plans using Control Query Defaults and Control Query Shape
Course outline
Module 1: SQL/MX query fundamentals
Query compilation phases
Branch and bound optimization
Query tree data flow model
Executor functionality in DP2
Forms of parallelism in a query plan
Module 2: Database definition
SQL/MX release 2 table definitions
Range and hash partitioning
Referential integrity
Triggers
Utilities to display database definitions
Lab exercise: Database definitions
Module 3: Viewing query execution plans
Visual query planner
Explain function and Display_Explain command
Fix explain and QPDrawer unsupported tools
Lab exercise: Viewing query plans
Module 4: File_Scan and exchange operators
Describe the basic operators that form a query plan: root operator, file scan operators, and exchange operators
Describe statement atomicity
Describe exchange operators and process boundaries
Lab exercise: Analyzing basic query plans
Module 5: Index_Scan Operators and MDAM
Index scan operators and their tokens
Alternate index access path
OR optimization
MultiDimensional Access Method (MDAM)
Forcing index access and MDAM with control query default and control query shape
Lab exercise: Analyzing queries that use index scan operators and MDAM
Module 6: Delete operators
Describe the delete operators and their tokens
Analyze query plans that use delete operators to delete rows from table with and without indexes
Lab exercise: Analyzing delete queries
Module 7: Update operators
Describe the update operators and their tokens
Analyze query plans that use update operators to update rows in a table with and without indexes
Lab exercise: Analyzing update queries
Module 8: Insert operators
Describe the insert operators and their tokens
Analyze query plans that use insert operators to insert rows in a table with and without indexes
Lab exercise: Analyzing insert queries
Module 9: Non-parallel joins
Describe the join operators (nested join, merge join, ordered hash join, and hash join and their tokens
Analyze query plans that use join operators
Describe the system defaults for controlling joins
Describe control query shape statement for joins
Lab exercise: Analyzing Non-parallel join queries
Module 10: Parallel joins
Describe type 1 parallel joins; matching partitions, logical partition grouping, logical sub partitioning, range repartitioning, and hash repartitioning
Describe type 2 parallel joins: replicate no broadcast and replicate via broadcast
Describe system defaults for controlling ESP parallelism
Lab exercise: Analyzing parallel join queries
Module 11: Sub query operators
Describe semi join and anti semi join operators and their tokens
Analyzing query plans that use the semi join and anti-semi join operators
Lab exercise: Analyzing sub queries
Module 12: Sorting and Grouping Operators
Sort, sort groupby, hash groupby operators and their tokens
Sort partial aggr, sort scalar aggr, and shortcut scalar aggr operators and their tokens
Display and analyze query plans that use the sort, sort groupby, hash groupby, sort partial aggr, sort scalar aggr, and shortcut scalar aggr operators
Lab exercise: Analyzing queries that use sorting and grouping operators
Module 13: Row sets and compound statements
Row set operators and tokens
Compound statements
Display and analyze query plans that use rows set arrays and compound statements
Lab exercise: Row sets and compound statements
Module 14: Referential integrity and triggers
Referential integrity
Triggers
Display and analyze insert, update, and delete queries on tables that have referential integrity constraints and triggers
Lab exercise: Referential integrity and triggers
Module 15: Forcing plans
Steps to analyzing query plans
Methods to change query plans: Control query default and control query shape
Examples of forcing query plans
Module 16: Tuning
Properties and costs
TP queries and OLT optimizations
Basic query, application, and database design for better performance
Onsite-Delivery Equipment Requirements
HP NonStop S72000 server or later that supports 1 GB of memory and IEEE floating point
G06.24 or later operating system and DDL licensing product T0394
Printable version
