Click on the action buttons in the action panel to get the query execution times for tables with and without MDC and generate a graph comparing the same
Results may vary depending on the volume of data. For optimal performance, use high volume of data.
To increase the volume of data, load the fact tables 'te_temp.subscriber_rated_usage' and 'te_temp.subscriber_dedicated_account_usage' with the corresponding .del files present in the LoadFiles directory.
Query execution times for tables enabled with DPF + Indexes + RP + MDC, DPF + Indexes + RP, DPF + Indexes, DPF + Indexes and DPF are computed
The query execution time would reduce with the creation of a MDC by clustering data across multiple dimensions
The graph here might show an increase in the query execution time with MDC. This is because the amount of data in the tables is very low. This causes the MDC blocks to be inadequately occupied. Also, the cost of retrieval of the data from these blocks is not compensated by the amount of data.
In a typical data warehouse environment consisting of millions of rows of data, the MDC would show a great reduction in execution time
An explain snapshot is compressed information that is collected when an SQL statement is explained. It contains
Run the following steps on the DB2 CLP window to view the explain snapshot :
designdb
.query_MDC.db2
replace 'SCHEMA' with the Default Schema.query_MDC.db2
designdb
-1 -o explain_MDC.out
. The Explain Plan can be viewed in the output file explain_MDC.out
.Click here to view the sample output of the Explain Access Plan : Sample Explain Access Plan