Question & Answer
Question
Answer
- Additional Details for Q3
The optimizer requires several executions of a query in order to learn from the repeated index advice before it can justify the cost of creating the autonomic index. The system must observe frequent runs before the benefits of using the index start to outweigh the cost of creating it. Once the optimizer decides to create an MTI, it takes time to build the index so the first execution of the query could be slow as the user has to wait for the MTI to be built. Temporary indexes can be shared across jobs and other queries executing on the system; therefore, MTI usage depends on which queries have historically run and caused an MTI to be built. For example, Query2 might build an MTI and Query1 starts using that MTI. Query1 is an important query and is now using index access; the users have come to expect the fast performing index access plan. Then, the MTI is removed, due to an IPL, and Query2 is no lower executed as much as it was before the IPL so the MTI has not reappeared. In the meantime, users of Query1 start to complain about the performance degradation in Query1. The solution is to build a permanent index for Query1
- Additional Details for Q4
The amortization algorithm means that you might see the optimizer initially pick a non-MTI access plan but over several executions of the query, the optimizer can reestimate the cost of building and maintaining an MTI and determine it is now low enough to warrant a new access plan that does use an MTI. Amortization algorithms help to balance the one-time cost of creating the MTI with the expected runtime savings of subsequent query runs

Related Information
Was this topic helpful?
Document Information
Modified date:
05 October 2022
UID
ibm16205533