Table of Contents
Informix.Performance Optimization
Overview:
Steps for Optimizing
Optimization Goal:.Increase Performance
Setting up a Test Environment
Optimizing the Query
Optimizing the Query:.Understand the Requirements
Optimizing the Query:.Examine the Schema
Optimizing the Query:.Examine the Data
Optimizing the Query:.Run, Examine and Modify
Set Explain Output
Set Explain: Example 1
Set Explain: Example 2
Set Explain: Example 3
Set Explain: Example 4
Set Explain: Example 5
Set Explain: Example 6
Additional Set Explain Output
Any Questions?
Indexing Strategies
Indexing Strategies: B+ Trees
Indexing Strategies:.Types of Indexes
Indexing Strategies:.Leading Portion of an Index
Indexing Strategies: .Guidelines
Indexing Strategies:.Benefits vs. Cost
Optimization Techniques
Optimization Techniques
Optimization Techniques (Cont.)
Optimization Techniques:.Use Composite Indexes
Optimization Techniques:.Use Index Filters
Optimization Techniques:.Use Index Filters
Optimization Techniques:.Use Index Filters
Optimization Techniques:.Use Index Filters
Optimization Techniques:.Use Index Filters
Optimization Techniques:.Indexed Reads for Sorting
Optimization Techniques:. Indexed Reads for Sorting
Optimization Techniques:. Indexed Reads for Sorting
Optimization Techniques:. Indexed Reads for Sorting
Optimization Techniques:.Temporary Tables
Optimization Techniques:.Temporary Tables
Optimization Techniques:.Using UNION’s
Optimization Techniques:. Using UNION’s
Optimization Techniques:.Avoid Sequential Scans
Optimization Techniques:.Drop and Recreate Indexes
Optimization Techniques:. Avoid Correlated Sub-queries
Optimization Techniques:. Avoid Correlated Sub-queries
Optimization Techniques:. Select needed columns .vs. Select *
Optimization Techniques:.Use Outer Joins
Optimization Techniques:.Prepare and Execute
Optimization Techniques:.Prepare and Execute
Any Questions?
New SQL for OnLine Dynamic Server 7.3
New SQL in ODS 7.3
New SQL in ODS 7.3:.First N rows
New SQL in ODS 7.3:.CASE
New SQL in ODS 7.3:.CASE
New SQL in ODS 7.3:.DECODE
New SQL in ODS 7.3:.NVL
Any Questions?
Table Joins
Types of Table Scans
Index Scans:.Upper and Lower Index Filters
Index Scans:.Upper and Lower Index Filters
Index Scans:.Upper and Lower Index Filters
Index Scans:.Key-Only
Index Scans: Key-First
Any Questions?
Joining Tables
Joining Tables: Join Methods
Join Methods: Sort Merge Join
Join Methods: Nested Loop Join
Join Methods: Nested Loop Join
Joining Tables: Table Order
Joining Tables: Table Order.Who Cares?
Joining Tables: Table Order.What is the best order?
Joining Tables: Table Order.What affects the join order?
Any Questions?
Optimizer Directives
Optimizer Directives
Optimizer Directives
Optimizer Directives:.Syntax
Types of Directives
Types of Directives:.Access Methods
Types of Directives:.Join Order
Types of Directives:.Join Methods
Directives Examples: ORDERED
Directives Examples: ORDERED
Directives Examples : INDEX
Directives Examples : INDEX (cont.)
Directives Examples : Errors
Any Questions?
Correlated Sub-Queries
Correlated Sub-Queries.What are they?
Correlated Sub-Queries.What are they?
Correlated Sub-Queries.What’s wrong with them?
Correlated Sub-queries
Correlated Sub-queries: .Normal CSQ
Correlated Sub-queries: .Rewritten CSQ
Correlated Sub-queries: .CSQ Flattening
Correlated Sub-queries: . Predicate Promotion in CSQs
Correlated Sub-queries: .Predicate Promotion in CSQs
Correlated Sub-queries: .Predicate Promotion in CSQs
Correlated Sub-Queries:.First Row/Semi-Join
Correlated Sub-Queries:.First Row/Semi-Join
Correlated Sub-Queries:.Skip Duplicate
Any Questions?
Want to know more?
PPT Slide
Enter Your Session ID
A More Complex Example
PPT Slide
|
