The Sybase Query Optimization concepts class provides the students with a detailed understanding of the methods by which the Sybase optimizer functions, and the hints that can be provided to the optimizer to achieve the most advantageous performance for Sybase queries. The optimizer steps of processing will be discussed in detail. The class will also cover statistics generation and usage, optimizations for single table queries as well as join queries, and the methodology to generate faster and more efficient code. Students will also obtain a detailed understanding of the usage and advantages of Sybase indexes.
Upon completion of the course, the students will have a detailed knowledge of the manner by which to achieve the most practical and efficient Sybase query processing through the optimizer.
Sybase Training Objectives:
Upon successful completion of this course, participants will be able to both understand how the Sybase optimizer works, and how to provide hints to the optimizer in order to obtain very efficient query processing.
The student will understand the internal means by which the optimizer processes, and will be knowledgeable of the advantages of Sybase indexes, the usage of optimizer tracing tools, and parallel processing in Sybase.
Sybase Training Prerequisites:
Students should be able to code Sybase SQL and Transact_SQL queries.
Sybase Training Audience:
This course is recommended for applications programmers, system and database administrators, database designers and all other related personnel who require an in-depth understanding of the means by which the Sybase optimizer functions. People who are interested in learning how to code efficient SQL statements in Sybase should also attend this course.
Course duration:
1 day
Sybase Training Course outline
Table structures
Data placement on disk
Optimizer functioning
Access methods
Usage of indexes
Explanation of different nodes
Statistics
Histograms
Execution plans
Execution plan options
Optimizer selectivity
Optimizer cardinality
Optimizer costs
Optimizer hints
Influencing the optimizer
Sort operations
Single table optimizations
Join - multi table optimizations
Nested iterations - sort/merge and hash joins
Optimizer processing for
- AND
- OR
- In Like Between
- Functions
- Wild cards
- Incompatible datatypes
- Composite indexes
- Variables
- Covered query processing
SARG transitive closure
Join transitive closure
Query reformatting
Stored procedure optimizations
Stored procedure recompiles
Optimizer forcing
Parallel queries
Sybase table partitioning
Abstract query plans concepts and usage
Deferred compilation
Monitoring and Diagnostic access ( MDA ) tables
- Their usage and advantages
In memory databases - concepts and usage
