SAP ABAP Data Element QQCIA (iSeries: Index Advice (Database Monitor))
Hierarchy
SAP_BASIS (Software Component) SAP Basis Component
   BC-DB-DB4 (Application Component) DB2 for AS/400
     STU4 (Package) CCMS / AS/400
Basic Data
Data Element QQCIA
Short Description iSeries: Index Advice (Database Monitor)  
Data Type
Category of Dictionary Type D   Domain
Type of Object Referenced     No Information
Domain / Name of Reference Type QQDEC150    
Data Type DEC   Counter or amount field with comma and sign 
Length 15    
Decimal Places 0    
Output Length 19    
Value Table      
Further Characteristics
Search Help: Name    
Search Help: Parameters    
Parameter ID   
Default Component name    
Change document    
No Input History    
Basic direction is set to LTR    
No BIDI Filtering    
Field Label
  Length  Field Label  
Short 11 Ind. Adv. 
Medium 21 Index Advice 
Long 41 Index Advice 
Heading 55 Index Advice 
Documentation

Definition

QQCIA is the number of executions of a statement where the database optimizer advised the creation of a permanent index to improve performance.

Use

Consider creating a permanent index with the suggested key field(s) if:

  • The query is executed frequently and
  • Query execution time can be greatly improved by using the index and
  • Overhead to maintain the index is not greater than query runtime saving

Caution

When a permanent index is created over a table, this index has to be maintained whenever an INSERT, DELETE or UPDATE statement is executed on that table. For tables that are frequently modified, the performance improvements with additional indexes for some statements should be weighed against the performance losses caused by the maintenance of these indexes for other statements.

Example

The Open SQL statement (with an appropriate internal table declaration for itab) would list all statements (library/SQL package/statement name) and physical files (tables) accessed that were implemented with a table scan and an index was advised when the most expensive execution occurred:
select b~qqplib b~qqpnam b~qqsnam a~qqptln a~qqptfn
into corrseponding fields of table itab
from SQ3000_DB4 as a
inner join SQ1000_DB4 as b
where a~qqkey = b~qqkey and a~qqidxa = 'Y'.

History
Last changed by/on SAP  20070813 
SAP Release Created in