I have been searching some proofs, that MLPPIs effect insert performance. According to that link, without actually saying why:
MLPPIs—provide an opportunity to significantly improve the performance of certain queries and high-volume insert, update and delete operations.
That article builds interesting cases how such indexes may be used to improve loading, but it doesn't look reliable as a proof.
In addition, I have performed my own investigation:
I have the table with 12 mln. rows with a date type field, so I created 2 new empty tables with and without partitioning on that date type field, but almost with the same structure as the original table, except partionating. Then I have inserted all these 12 mln to them. Explaining gave exactly same result 3 min+. Artificially trying to insert all these 12 mln rows into one partition by explicitly pointing date haven't changed the time.
So my question is: may MLPPIs increase performance of inserts and why?
You should consider this article as reliable, Paul Sinclair was the release architect for MLPPI :)
When you Insert into an empty table you will hardly notice any difference with or without partitioning. In fact PPI might be slower if the source table has the same PI and is not partitioned due to the re-sort.
But when you Insert into a populated table (or Update) and all rows go to a few partitions, it's faster because you skip over unused partitions and read (& probably write) less datablocks, i.e. the number of inserted (or updated) rows per block is much higher.
And if you Insert into an empty partition it's using FastPath processing (like an empty table) and avoids Transient Journaling.