While testing the 13.10 version of the DBMS on our staging system, we found a query that performs much better under 13.10 and is using a "Group AMP Join", where the 12.0 version Explain plan references something completely different. I am not familiar with the Group AMP Join and have been unsuccessful finding it in the 13.10 documentation searches I have performed. Can someone explain this Join type and why/when the optimizer would chose it? Is it related to the DBMS or controled by a DBS Control Parameter?
A "Group-AMP" join occurs when the optimizer detects that it has a few hash values that will be involved, but not enough to warrant an "all-AMP" join. The advantage is that there is less communication to set up the join since only those AMP's where rows will be redistributed to will participate in the redistribution and the subsequent join step. Also, because it's a "Group-AMP" operation, the locking occurs at the row hash level and not the table level as in an all-AMP operation.
A good example of this would be a table where you had a unique primary index and then you had another column that was highly unique that you set up a join index on (with just the highly unique column and the ROWID). If you run a query where you specify the value for the highly-unique column, it will access that join index (via single-AMP) and then use a "Group-AMP" join to join back to the base table via the unique primary index. Because the optimizer knows that there are only a few row hash values involved in the table, it will use the "Group-AMP" operation.
The "Group-AMP" operation has been around for a few releases..at least since R12 and maybe sooner than that.
Hope that helps.