I have a create table query which creates a table with records (multiset), and a secondary query that inserts records into it (millions of rows from the first, hundreds of thousands from the second). The explain seems to indicate better performance when doing this vs running the same queries using set table with aggregates (results in half the rows), but it is difficult to actually test for each since during the week there is a high volume of use on the system & queries can take sometimes an hour to run, but on weekend volume is so low the queries run in seconds. Multiset results in no aggregate operations, but a whole lot more rows to insert...set with aggregate requires more resources to aggregate but results in much fewer rows. From a technical standpoint, which would be more efficient?
My two cents...
Consider the index selections is same for both set and multiset cases. The multiset would be faster as there is no extra processing even checking the duplicate rows is not required. First of all Aggregation is a resource intensive process and during the insertions the duplicate rows check will require some extra time/processing and will take more ececution time.
You have not provided information about the indexes, the second confusing thing is whether you are trying to insert the individual rows, or you need to insert aggregates? What I suppose is you want to insert aggregate rows, So if the index on the target table is UPI, then in case of SET tables, dup check will be reduced to UPI only and will be much faster.
In case of multiset the duplicates will not be inserted due to UPI. So please confirm if the Index of the table and also if you need to retain the Duplicate rows or just individual rows?