Caché SQL Optimization Guide
Contents
 
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

 
Contents

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.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 Average Field Size
  • 3.2.9 Map BlockCount Tab
  • 3.2.10 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
  • 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 SQL Statements
  • 7.1.1 Operations that Create SQL Statements
  • 7.1.2 Listing SQL Statements
  • 7.1.3 SQL Statement Text
  • 7.1.4 Data Definition (DDL) SQL Statements
  • 7.1.5 Data Management (DML) SQL Statements
  • 7.1.6 Delete Table and SQL Statements
  • 7.1.7 Querying the SQL Statements
  • 7.2 Frozen Plans
  • 7.2.1 How to Use Frozen Plans
  • 7.2.2 Frozen Plans Interface
  • 7.2.3 Privileges
  • 7.2.4 SQL Statement Details
  • 7.2.5 Frozen Plan in Error
  • 7.2.6 %NOFPLAN Keyword
  • 7.3 Exporting and Importing Frozen Plans