Which recommendation will help the merge step?

A customer is complaining that the creation of a large table in the lab environment is getting stuck in the merge step. The customer is using the following command to create the table: CREATE TABLE … AS (SELECT * …) WITH DATA

Which recommendation will help the merge step?
A . A column that provides good distribution should be specified as PRIMARY INDEX.
B . The MERGEBLOCKRATIO should be specified for the table to improve the data block merge operation.
C . The table should be created with "CREATE TABLE … AS (SELECT *…) WITH NO DATA", and afterwards, "INSERT … SELECT *" should be used to fill the table.
D . Users of the lab may have lower priority than other workloads, so the creation of the table should be moved to off-peak hours.

View Answer

Answer: C

Explanation:

Merge step issues often occur when a large amount of data is being processed during the table creation, especially if the system is trying to simultaneously create the table and insert data.

By using "WITH NO DATA", the table structure is created first, without the actual data being inserted during the table creation process. The *"INSERT … SELECT " command can then be used afterwards to populate the table in a more controlled way, reducing the load on the system during the creation phase and potentially improving the efficiency of the merge step.

Specifying a good distribution for the primary index can help overall performance, but it doesn’t directly address the issue with the merge step in this scenario.

Specifying the MERGEBLOCKRATIO isn’t typically a solution for this specific problem; the merge block ratio is more about the optimization of data block merges rather than the creation of tables.

Moving the creation to off-peak hours may help if the environment is busy, but it doesn’t directly address the core issue of the merge step getting stuck.