The Workload Table Organization Advisor recommends converting
row-organized tables to column-organized tables.
About this task
The advisor examines the all of the tables that are referenced
by the statements that are in a query workload. Its recommendations
lead to the best estimated performance improvement for the query workload
as a whole.
The advisor presents its analysis and rationales
so that you can see the tables that are recommended for conversion,
as well as those tables that are not. You can see the cardinalities
of the tables, how many statements in the query workload reference
them, the cumulative cost of running the statements that reference
a table, the statements themselves, and more.
You can also view
and save the DDL for implementing the changes that are necessary to
convert the tables.
Tip: Run this advisor only after
you find that recommendations from the Workload Statistics Advisor,
the Workload Index Advisor, or both do not improve performance enough.
You might be able to reach the required performance for the query
workload by gathering statistics, creating or modifying indexes, or
both. Moreover, the Workload Table Organization Advisor relies on
correct statistics when it generates its recommendations.
To generate and act on recommendations for converting
tables to column organization:
Results
This section displays the tables that
are and are not recommended for migration to column organization.
At the top of the section is an estimation of the improvement
to the performance of the entire query workload.
The advisor calculates the estimated
performance improvement by following these steps: - Assume that all of the recommended table conversions are performed,
and that the indexes on those tables are dropped.
- Find the product of the following two values for each statement
in a query workload: Number of executions * Statement cost
- Add the products together.
These columns are displayed in the table
at the top of this section:
- Table
- The name of a row-organized table that is either recommended for
conversion to column organization or is recommended to remain as is.
- Creator
- The creator of the table.
- Current Organization
- Indicates whether the table is currently row-organized or column-organized.
- Recommended Organization
- Indicates whether the table is recommended for conversion to column
organization or is recommended to remain as is.
- Warning
- For tables that are recommended for conversion, describes changes
that can occur as a result of the conversion.
- Indexes will be removed.
- Indexes are not allowed on column-organized tables.
- Data partitioning will be lost.
- You can restore data partitioning only if you convert the table
back to row-organization.
- ENFORCED RI constraints will change to NOT ENFORCED.
- It will be possible for errant data to be inserted into the table.
- ENFORCED CHECK constraints will change to NOT ENFORCED.
- It will be possible for errant data to be inserted into the table.
- Table space will be changed.
- After the conversion, the table will be in the table space that
is specified in the preferences for the Workload Table Organization
Advisor or in a table space that the advisor selected. You can modify
the conversion script to use a different table space.
- MQTs will be removed.
- Even after the removal of MQTs, SQL statements will be able to
access data in the table faster.
- Cardinality
- The number of rows that are in the table.
- References to Table
- The number of time that the SQL statements that are in the query
workload reference the table.
- Cumulative Total Cost
- The sum of the CPU costs in timerons of each access to data in
the table by the SQL statements that are in the query workload.
- Affected Statements
- The number of SQL statements in the query workload that reference
the table.
- Finding
- Explains why the table is or is not recommended for conversion.
- The table is already column-organized.
- No conversion is necessary.
- Conversion would not improve performance.
- The performance of the query workload would not improve if the
table were converted to column-organization.
- Conversion would improve performance.
- The performance of the query workload would improve if the table
were converted to column-organization.
- Column-organization restrictions do not allow conversion.
- The following table types cannot be converted to column-organized
tables: MQT, declared global temporary table, created global temporary
table.
There are other restrictions, too. For example, tables with
LOB columns cannot be converted, not can tables that are used in MERGE
statements or in statements that declare cursors. For the full set
of restrictions, see the documentation for DB2 10.5 for Linux, UNIX,
and Windows or later.
- Missing statistics do not allow conversion.
- The table is missing statistics, so conversion cannot be done
reliably. Run the Workload Statistics Advisor to collect statistics
for the query workload, run the RUNSTATS statements that the advisor
recommends, and then run the Workload Table Organization Advisor again.
These columns appear in the table SQL
Statements Affected. The table lists all of the SQL statements
in the query workload that would be affected by the conversion of
the listed tables to column-organization.
- Execution Count
- The number of times that the SQL statement is run in the query
workload.
- Weight
- The relative weight of the statement in the query workload. The
value is calculated by multiplying the value in the Cost
Before column by the execution count, and then dividing
the product by the total "cost before" for the query workload.
- Estimated Performance Gain
- The estimated increase in the performance of the statement that
accesses the selected table, if the recommendation for conversion
is implemented.
- For example, an estimated performance gain of 90.61% means that
90.61% less CPU time is required for the statement to access the selected
table.
- Cost Before
- The cost of the statement in timerons before the conversion of
the referenced table to a column-organized table.
- Cost After
- The cost of the statement in timerons after the conversion of
the referenced table to a column-organized table.
- SQL Statement
- The text of the statement. To see the full text of the statement,
right-click the row and select Show SQL Statement.
What to do next
Evaluate the estimated improvements in the performance of
the query workload to find out whether they might help you to meet
your performance requirements. If you decide to follow the recommendations,
ensure that your database is backed up before you run the script that
the advisor generated. Also, be aware that referential integrity constraints
on tables will be removed during the conversion to column-organization.