Goals

  • Implement an optimum table and index configuration through the use of best design practices, the Index Advisor, and database options
  • Understand the methods of loading and changing data
  • Analyze IQ data with OLAP functions
  • Program stored procedures, custom functions, and events
  • Understand the design and functionality of the IQ query engine and optimization process
  • Interpret query plans and timing diagrams
  • Identify friction points in query evaluation, table joins, and other plan operators
  • Describe parallelism in IQ
  • Influence the optimizer to improve query performance

Audience

  • Query Developers

Prerequisites

Essential

  • Understanding of database concepts and database fundamentals
  • Familiarity with any SQL dialect

Recommended

  • None

Course based on software release

  • SAP IQ 16 Service Pack 3

Content

  • SAP IQ Overview
    • SAP IQ Overview
  • Introduction to Watcom-SQL
    • Identify the differences between SQL dialects
    • Identify the language elements and conventions of Watcom-SQL
    • Describe IQ joins
  • Creating Tables
    • Create, drop, and alter tables
    • Create and drop indexes
    • Create, drop, and merge table partitions
    • Use temporary tables and system tables
  • Designing Indexes
    • Explain how indexes work in IQ
    • Recognize the best indexes to use based on query & data type
    • Use the Index Advisor to improve index designs
    • Monitor indexes to determine effectiveness
  • Manipulating Data
    • Load data into tables
    • Describe and resolve issues related to loading data
    • Use the UPDATE statement
    • Delete data from a table
    • Export data from an IQ database
  • Functions and Procedures
    • Identify the types of built-in functions supported by IQ
    • Create user-defined functions
    • Identify IQ procedures and how they are used
  • Analyzing Data
    • Explain the benefits of using the OLAP functions
    • Identify how to use GROUP BY ROLLUP and CUBE operators
    • Use windowed functions to analyze data
  • Query Processing Overview
    • Describe the IQ query engine architecture
    • Explain index-based evaluation
    • Explain data projection
    • Describe how data flows through a query tree
    • Identify query tree operators
    • Generate query plans
  • Timing and Execution
    • Create query timing diagrams
    • Identify segments of query execution to be tuned
  • Query Plan Nodes
    • Read a query plan
    • Describe the nodes of a query plan
    • Describe how predicates and indexes interact in queries
  • Joins
    • Explain join node functionality
    • Identify how joins are executed in a query
    • Identify join costs
  • Other Operators
    • Group By and equivalent nodes
    • Describe parallel query operators
  • Tuning
    • Controlling optimization with database options
    • Influencing optimization in queries