When a table is large and has no useful index for a join, the optimizer considers a sort merge join, and also considers creating and sorting a worktable, and using a nested-loop join.
The process of generating a worktable with a clustered index and performing a nested-loop join is known as reformatting.
Like a sort-merge join, reformatting scans the tables and copies qualifying rows to a worktable. But instead of the sort and merge used for a merge join, Adaptive Server creates a temporary clustered index on the join column for the inner table. In some cases, creating and using the clustered index is cheaper than a sort-merge join.
The steps in the reformatting strategy are:
Creating a worktable
Inserting the needed columns from the qualifying rows
Creating a clustered index on the join columns of the worktable
Using the clustered index in the join to retrieve the qualifying rows from each table
The main cost of the reformatting strategy is the time and I/O necessary to create the worktable and to build the clustered index on the worktable. Adaptive Server uses reformatting only when the reformatting cost is less than the cost of a merge join or repeated table scans.
A showplan message indicates when Adaptive Server is using the reformatting strategy and includes other messages showing the steps used to build the worktables.
