To perform unique reformatting, Adaptive Server:
Selects rows into a worktable and sorts the worktable, removing duplicates and creating a clustered index on the join key.
Joins the worktable with the next table in the join order. If there is a nonunique index on publishers.pub_id, this query can use a unique reformat strategy:
select title_id from titles where pub_id in (select pub_id from publishers where state = "TX")
This query is executed as:
select pub_id into #publishers from publishers where state = "TX"
And after the sort removes duplicates and creates the clustered index:
select title_id from titles, #publishers where titles.pub_id = #publishers.pub_id
showplan messages for unique reformatting show “Worktable created for REFORMATTING” in Step 1, and “Using Clustered Index” on the worktable in Step 2.
dbcc traceon(310) displays “REFORMATTING with Unique Reformatting” for the method for the publishers table.