Caché SQL Optimization Guide
Contents
[Home]  [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

Preface : 
 
 
Chapter 1: 
  1.1 Table Definition Optimization
1.2 Table Data Optimization
1.3 Query Optimization
1.4 Configuration Optimization
Chapter 2: 
  2.1 Overview
2.1.1 Index Attributes
2.1.2 Storage Type and Indices
2.2 Automatically-Defined Indices
2.2.1 Bitmap Extent Index
2.3 Defining Indices
2.3.1 Defining Indices Using a Class Definition
2.3.2 Defining Indices Using DDL
2.4 Bitmap Indices
2.4.1 Bitmap Index Operation
2.4.2 Defining a Bitmap Index Using a Class Definition
2.4.3 Defining Bitmap Indices Using DDL
2.4.4 Generating a Bitmap Extent Index
2.4.5 Choosing an Index Type
2.4.6 Restrictions on Bitmap Indices
2.4.7 Maintaining Bitmap Indices
2.5 Bitslice Indices
2.6 Building Indices
2.6.1 Building Indices on an Inactive System
2.6.2 Building Indices on a READONLY Active System
2.6.3 Building Indices on a READ and WRITE Active System
2.7 Validating Indices
2.7.1 Validating Indices by Name
2.8 Open, Exists, and Delete Methods
2.8.1 Opening an Instance by Index Key
2.8.2 Checking If an Instance Exists
2.8.3 Deleting an Instance
Chapter 3: 
  3.1 ExtentSize, Selectivity, and BlockCount
3.1.1 ExtentSize
3.1.2 Selectivity
3.1.3 BlockCount
3.2 Tune Table
3.2.1 When to Run Tune Table
3.2.2 Running Tune Table
3.2.3 Running Tune Table on Live Data
3.2.4 Extent Size and the Row Count
3.2.5 CALCSELECTIVITY Parameter and Not Calculating Selectivity
3.2.6 Selectivity and Outlier Selectivity
3.2.7 Outlier Optimization
3.2.8 The Notes Column
3.2.9 Average Field Size
3.2.10 Map BlockCount Tab
3.2.11 Exporting and Re-importing Tune Table Statistics
Chapter 4: 
  4.1 Cached Queries Improve Performance
4.1.1 Cached Query Names
4.2 Literal Substitution
4.2.1 Literal Substitution and Performance
4.2.2 Suppressing Literal Substitution
4.3 Cached Query Result Set
4.4 Existing Cached Queries
4.4.1 Counting Cached Queries
4.4.2 Displaying Cached Queries
4.4.3 Listing Cached Queries to a File
4.5 Executing Cached Queries
4.6 Cached Query Lock
4.7 Purging Cached Queries
4.7.1 Remote Systems
4.8 SQL Statements That Are Not Cached
Chapter 5: 
  5.1 Management Portal SQL Performance Tools
5.2 SQL Runtime Statistics
5.2.1 Gather Query Performance Statistics
5.2.2 Display Query Performance Statistics
5.2.3 Runtime Statistics and Show Plan
5.3 Using Indices
5.3.1 What to Index
5.3.2 Index Configuration Options
5.3.3 Index Usage Analysis
5.3.4 Index Analyzer
5.3.5 IndexUsage() Method
5.3.6 Index Optimization Options
5.4 Show Plan
5.4.1 Displaying an Execution Plan
5.4.2 Execution Plan: Statement Text and Query Plan
5.5 Alternate Show Plans
5.5.1 Stats
5.6 Writing Query Optimization Plans to a File
5.7 Parallel Query Processing
5.7.1 %PARALLEL Keyword Ignored
5.7.2 %PARALLEL in Subqueries
5.7.3 Shared Memory Considerations
5.7.4 Cached Query Considerations
5.7.5 SQL Statements and Plan State
Chapter 6: 
  6.1 Tables Stored In Maps
6.2 Developing the Plan
6.3 Reading the Plan
6.3.1 Accessing Maps
6.3.2 Conditions and Expressions
6.3.3 Loops
6.3.4 Temporary Files
6.3.5 Modules
6.3.6 Queries Sent for Processing
6.3.7 Sub-Queries, JOINs and UNIONs
6.4 Plan Analysis
6.4.1 Adding an Index
6.4.2 Adding Fields to Index Data
6.4.3 Adding a Join Index
Chapter 7: 
  7.1 Operations that Create SQL Statements
7.1.1 Other SQL Statement Operations
7.2 Listing SQL Statements
7.2.1 Plan State
7.2.2 SQL Statement Text
7.3 Data Management (DML) SQL Statements
7.3.1 Modifying a Routine Containing Embedded SQL
7.3.2 SELECT Commands
7.4 SQL Statement Details
7.4.1 Statement Details Section
7.4.2 Performance Statistics
7.4.3 Compile Settings Section
7.4.4 Routines and Relations Sections
7.5 Data Definition (DDL) SQL Statements
7.6 Delete Table and SQL Statements
7.7 Querying the SQL Statements
Chapter 8: 
  8.1 How to Use Frozen Plans
8.1.1 Caché Version Upgrade Automatically Freezes Plans
8.2 Frozen Plans Interface
8.2.1 Privileges
8.2.2 Frozen Plan Different
8.2.3 Frozen Plan in Error
8.3 %NOFPLAN Keyword
8.4 Exporting and Importing Frozen Plans