Skip to content

Instantly share code, notes, and snippets.

@forstie
Created June 19, 2019 04:22
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save forstie/148fb44f657aab878eb44bc88f0e00d9 to your computer and use it in GitHub Desktop.
Save forstie/148fb44f657aab878eb44bc88f0e00d9 to your computer and use it in GitHub Desktop.
Maintained Temporary Index related index advice since the last IPL
@SvKN0
Copy link

SvKN0 commented Nov 25, 2021

Hi, Scott.
I'm not shore that it is the right place for this question...
I'm facing with discrepancy in results received from different sources about MTI.
for example for one file the query above returns one row:
image
But other methods show different results:
query: select INDEX_NAME from qsys2.syspartitionindexes where table_name = 'FPGPF' and index_type = 'TEMPORARY'
result: 2 MAINTAINED TEMPORARY INDEXES
dump plan cache shows two MTIs on this table used in SQL queries:
image
Using ACS - SQL Performance Center I found plan that contains the lost MTI:
image
This situation is typical for many files. Sometimes the results are different between all these methodsIs. Is qsys2.condidxa shous correct information? Where is the truth?
MTI_INFO is not ready.. how to use QQQOOOCACH with "I" is not clear...

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment