Hi @pooja_bhumandla 

Thanks for such a nice and detailed description of Your case, that really helps to understand the scenario 🙂

Regarding Your questions:

1)

Overall operation could become more complex due to:

a) deletion vector creation and maintenance,

b) metadata operations,

c) any background optimization processes.

2)

ZORDER operation is phisically redistributing Your rows across new files (creating new delta table version) created by OPTIMIZE command (thats why we can see 7 files rewritten in case 2) in a given order - this is why your scan operations are faster (improved reading capabilities on zorder-ed table).

Merge operation had to rewrite more files and more data so it took longer and downgraded overall performance of Your operation (your rows got redistributed across multiple parquet files).

3)

I would recommend performing below checks / steps:

a) ZORDER keys are in line with merge patterns (keys),

b) use smaller target file sizes (using tuneFileSizesForRewrites or maxFileSize) - data layout under your delta table can have huge impact on merge performance (ref to this topic: delta-lake-file-sizes-optimize-maxfilesize)

c) consider running a test with liquid clustering or different partition alignment wich will fit your merge process,

d) test on hive-style partitioned and optimized table with no zorder and different maxFileSize (32 MB, 64MB, 128 MB - depends of your table size) or tuneFileSizesForRewrites set to true,

e) test and adapt what works best for Your specific scenario.

Best,

Radek.