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 |
