IBM Support

SQL Maintained Temporary Indexes

Question & Answer


Question

Db2® for IBM® i Temporary Index Question & Answer

Answer

Q1: What is a Maintained Temporary Index (MTI)?
A1: The Db2 for IBM i Query Optimizer depends on SQL indexes (CREATE INDEX) or DDS Logical Files (CRTLF) for good performance.  The indexes can be created by the user or by the system.  When user created indexes are not available, the optimizer can choose to create binary radix indexes, without any user intervention.  The system (optimizer) generated index is called a Maintained Temporary Index (MTI).   Temporary indexes, like permanent indexes, are maintained, meaning that any change in the data space is immediately reflected in the MTI.  Also, MTIs can be shared across several different queries.  Indexes are vital to the query optimizer as they improve the ability to read rows in keyed order or to probe for specific values, thus improving the performance of an SQL query.  

Q2:  Is it a good idea to rely on MTIs for good performance?
A2:  No.  The query optimizer performs best when the database engineer implements an indexing strategy that consists of permanent indexes.  The problem with relying on the optimizer to build indexes on-demand is: 
a) The initial execution of the query observes higher response time as the temporary index is being built (versus the database engineer pre-building a permanent index before executing the queries).
b) System resources are needed to build an MTI and if the MTI is being built over very large tables (physical file), there is potential for a system-wide performance impact
c) The index is temporary and will go away at some point.  Once it goes away, the optimizer often needs to see several executions of the query before an MTI access plan is considered again.
d) MTIs consume temporary space

Q3: If technology exists to automatically create indexes, why do I need to be concerned about permanent indexes?
A3: If your database applications depend on stable and impressive performance, then the application design should not rely on temporary data access methods.  A temporary indexing strategy introduces fluctuation in query performance.  The primary reason for the fluctuation is temporary indexes can be deleted by the system and might not reappear until several runs of the query are experienced. 
  • 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 longer 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

Q4: When will the optimizer build an MTI?
A4: The Db2 for IBM i Query optimizer makes its decision, to build an MTI, based on estimates and how many times a query is run.  During the optimization phase, the optimizer might estimate the cost to build and use an MTI  as too expensive.   However, if the query is run several times, the estimated cost to build the MTI is reduced and use of the MTI might now be justified.  Therefore, the estimated cost to use MTIs is amortized over time. 
  • 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

Q5: When will the temporary index be deleted?
A5: MTIs are eligible for deletion at IPL time or when all access plans, referencing the MTI, are purged from the plan cache.  After an IPL, it takes time for queries to warm up as all access plans have to be recreated.  It could also take time to repopulate the plan cache with temporary indexes.

Q6: If an MTI is deleted, will my query performance fluctuate?
A6: Probably.  The estimated cost to build an MTI is amortized over several runs of a query.  This means that if an MTI is deleted, then the next time we optimize the query that was using that MTI, our amortization method will start over with the first estimated cost of using an MTI being higher then the cost calculated on subsequent re-optimizations.  Also, even if a particular query was not using an MTI in the access plan, the SQL query engine can use temporary indexes as a source of statistics so if we no longer have access to the statistics, it is possible to see fluctuations in query response time.
 
Q7:  Will the optimizer advise a permanent index if an MTI is built?
A7: Most likely yes, although there are some scenarios where the optimizer would not advise a permanent index.  The Index Advisor has columns that indicate whether an MTI was used for the index being advised.  IBM advises our clients to follow query performance best practices of using the Advised Indexes table in ACS (Access Client Solutions) as part of their indexing strategy.  More details can be found in our Indexing & Statistics Strategies Paper & Discover MTIs & Replace them with Permanent Indexes
image 3401

Q8: Where can my database engineer learn about an indexing strategy:
A8: See the Indexing and Statistics Strategy white paper or partner with our Expert Labs Team to develop an indexing strategy.

Q9:  Are there differences between temporary indexes and permanent indexes?
A9:  Yes.  The first difference is the cost to build the MTI is often not accurate.  The optimizer does not have a way of knowing how the radix index will be built, and the actual data included in the MTI can dramatically change the size and time of the build.  The second difference is MTIs use temporary storage and they are managed entirely by the optimizer.  They are created whenever the optimizer needs them and deleted when the optimizer is finished with them.  Lastly, if the query environment uses ALWCPYDTA(*NO) or a SENSITIVE cursor, then MTIs are only reused until the cursor closes and cannot be shared by other queries or across jobs.  If MTIs are not reused, then we could see system resources being consumed, at a high rate, leading to possible system wide performance issues (see this IBM Developer article for more details).

Q10: How can I review the MTIs on my system?
A10: The MTI_INFO table function can be used to analyze the temporary indexes.

set schema <xxx>
create table mti_info as (select * from table(qsys2.MTI_INFO('*ALL', '*ALL')) ) with data;

/* Note: MTIs disappear every IPL so that date is always relevant when looking at MTI_INFO */
 -- To find MTIs greater than 1G (larger MTIs take time to build)
  select * from mti_info where mti_size > 1000000000;

 -- MTIs that currently are the most active; the reference count is like an in use count
    select * from mti_info x order by reference_count desc;

 --MTIs used by the most queries, length of QRO hash identifies MTIs serviced by the most variety of queries
    select length(qro_hash_json), x.* from mti_info x order by 1 desc;

 -- MTIs that are the longest lived (current date is most relevant to when the snapshot of MTIs was taken, for example the snap is weeks old, would adjust current to
     near that date
     select current_date - date(create_time), x.* from mti_info x order by 1 desc;

 


[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CKdAAM","label":"Performance-\u003EDatabase Performance"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]

Document Information

More support for:
IBM i

Component:
Performance->Database Performance

Software version:
All Versions

Operating system(s):
IBM i

Document number:
6205533

Modified date:
28 February 2025

UID

ibm16205533

Manage My Notification Subscriptions

Page Feedback