If the dataspace is deleted or if all related plans are evicted from the plan cache, the MTI will also be deleted. The lifecycle of shared MTIs is tied to the existence of the underlying dataspace and to the presence in the SQL plan cache of one or more queries that use the MTI. These MTIs are shared: they will be seen by and can be used by other queries. If the estimated cost to build the MTI seems reasonable to the optimizer, it will generate the MTI and then use it to run the query. The first circumstance that might produce MTIs is when the optimizer decides that an MTI can provide a more efficient implementation for a query than existing indexes can provide. Understanding each circumstance-and whether it applies to your workload-is crucial to effectively managing your usage of MTIs. But, if we drill down into this single basic reason, we’ll find a couple of distinct circumstances that cause the optimizer to need an index. If you have the right indexes on your tables, the optimizer won’t need to build an MTI.
In simplest terms, there is only one reason for the optimizer to build an MTI: no suitable permanent index exists to meet the requirements of a query. They are created whenever the optimizer needs them and deleted when the optimizer is finished with them. But they differ from permanent indexes in two important ways: they use temporary storage, and they are managed entirely by the optimizer. In many cases, MTIs can be shared between queries, just like permanent indexes. They are actively maintained, meaning that any change in the dataspace is immediately reflected in the MTI. Like any permanent index, the actual size of an MTI is heavily dependent on the keys included in the index and on the underlying data. Internally, they are radix indexes, just like permanent (that is, user created) indexes, and they provide the ability to read rows in keyed order or to probe for specific values. They function like any other index on the system. Maintained Temporary Indexes (MTIs) are indexes that the optimizer creates without any user intervention. This bucket reports the amount of storage used across the system by maintained temporary indexes. Of these buckets, this article focuses on the one labeled, *DATABASE DSI SQE MTI.
There are five global buckets that directly reflect usage by the SQL optimizer. We showed how the QSYS2.SYSTMPSTG system view provides a high-level overview of temporary storage across the system. In part 1 of this series, we described the kinds of temporary storage that the SQL optimizer uses. Maintained Temporary Indexes briefly explained
0 kommentar(er)
