Is `OPTIMIZE TABLE FINAL` required for mutations?

[Alex-Cheng]

After running query of mutations, such as alter table update ..., shall I run OPTIMIZE TABLE FINAL? Does the mutations produce multiple parts for single partition? And I could use OPTIMIZE to merge these parts into one?

Thank you.

[den-crane]

After running query of mutations, such as alter table update ..., shall I run OPTIMIZE TABLE FINAL?

No, you shall not.

Does the mutations produce multiple parts for single partition?

No. Each part is processed by a mutation separately. You get the same number of after a mutation which you had before.

And I could use OPTIMIZE to merge these parts into one?

You can but it's not recommended.

[Alex-Cheng]

My experience is that after I inserted a lot of data, then run a query including aggregation or rowNumberInAllBlocks() then I will get uncertain results. If I run optimize after insertion, then the query result is certain and correct.

And it is suspicious that some query won't produce exactly correct result without OPTIMIZE FINAL after executed ALTER TABLE UPDATE mutations. But this issue is hard to reproduce.

[den-crane]

Probably because you have multiple parts (before optimize) rowNumberInAllBlocks produces block dependent result.

[Alex-Cheng]

Probably because you have multiple parts (before optimize) rowNumberInAllBlocks produces block dependent result.

I think so

[den-crane]

Probably because you have multiple parts (before optimize) rowNumberInAllBlocks produces block dependent result.

BTW, It could be because the query is incorrect, and rowNumberInAllBlocks is used before ORDERBY.

[Alex-Cheng]

rowNumberInAllBlocks is used before ORDERBY

My query is select ..., rowNumberInAllBlocks(), ... from t1 order by ...