Design Advisor limitations and restrictions
There are certain limitations and restrictions associated with Design Advisor, about indexes, materialized query tables (MQTs), multidimensional clustering (MDC) tables, and database partitioning.
Restrictions on index
- Indexes that are suggested for MQTs are designed to improve workload performance, not MQT refresh performance.
- A clustering RID index is for MDC tables. The Design Advisor will include clustering RID indexes as an option rather than create an MDC structure for the table.
- The Version 9.7 Design Advisor does not suggest you use partitioned indexes on a partitioned table. All indexes are must be used with an explicit NOT PARTITIONED clause.
Restrictions on MQT
- The Design Advisor does not suggest the use of incremental MQTs. If you want to create incremental MQTs, you can convert REFRESH IMMEDIATE MQTs into incremental MQTs with your choice of staging tables.
- Indexes that are for MQTs are designed to improve workload performance, not MQT refresh performance.
- If update, insert, or delete operations are not included in the workload, the performance impact of updating a REFRESH IMMEDIATE MQT is not considered.
- It is suggested that REFRESH IMMEDIATE MQTs have unique indexes created on the implied unique key, which is composed of the columns in the GROUP BY clause of the MQT query definition.
Restrictions on MDC
- An existing table must be populated with sufficient data before the Design Advisor considers MDC for the table. A minimum of twenty to thirty megabytes of data is suggested. Tables that are smaller than 12 extents are excluded from consideration.
- MDC requirements for new MQTs will not be considered unless the sampling option, -r, is used with the db2advis command.
- The Design Advisor does not make MDC suggestions for typed, temporary, or federated tables.
- Sufficient storage space (approximately 1% of the table data for large tables) must be available for the sampling data that is used during the execution of the db2advis command.
- Tables that have not had statistics collected are excluded from consideration.
- The Design Advisor does not make suggestions for multicolumn dimensions.
Restrictions on database partitioning
The Design Advisor provides advise about database partitioning only for Db2® Enterprise Server Edition.
Temporary simulation catalog tables are created when the Design Advisor runs. An incomplete run can result in some of these tables not being dropped. In this situation, you can use the Design Advisor to drop these tables by restarting the utility. To remove the simulation catalog tables, specify both the -f option and the -n option (for -n, specifying the same user name that was used for the incomplete execution). If you do not specify the -f option, the Design Advisor will only generate the DROP statements that are required to remove the tables; it will not actually remove them.
Note: As of Version 9.5, the -f option is the default. This means that if you run db2advis with the MQT selection, the database manager automatically drops all local simulation catalog tables using the same user ID as the schema name.
You should create a separate table space on the catalog database partition for storing these simulated catalog tables, and set the DROPPED TABLE RECOVERY option on the CREATE or ALTER TABLESPACE statement to OFF. This enables easier cleanup and faster Design Advisor execution.