showplan messages for each type of merge join appear as specific combinations:
Full-merge join – there are no “FROM TABLE Worktable” messages, only the “inner table” and “outer table” messages for base tables in the query.
Right-merge join – the “outer table” is always a worktable.
Left-merge join – the “inner table” is always a worktable.
Sort-merge join – both tables are worktables.
For more information, see “Messages describing access methods, caching, and I/O cost” on page 93 in the book Performance and Tuning: Monitoring and Analyzing for Performance.
