Hopefully, this helps out?
CREATE TABLE first
(
one_id INT NOT NULL,
two_id INT NOT NULL,
date DATETIME NOT NULL,
junk_id INT NOT NULL,
CONSTRAINT first_pkc_1
PRIMARY KEY CLUSTERED ( one_id, two_id, date, junk_id ) on
'default'
)
LOCK allpages ON 'default' partition BY roundrobin 1
GO
CREATE TABLE second
(
junk_id INT NOT NULL,
junk_desc varchar(255) NULL,
CONSTRAINT second_pkc_1
PRIMARY KEY CLUSTERED ( junk_id ) on 'default'
)
LOCK allpages ON 'default' partition BY roundrobin 1
GO
select
a.one_id,
a.two_id,
a.date,
b.junk_id
from
first a
left join second b on a.junk_id = b.junk_id
where
a.date >= '20091020'
and
a.date <= '20091120'
and
a.one_id = 120326
and
a.two_id = 1
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
5 operator(s) under root
|ROOT:EMIT Operator (VA = 5)
|
| |RESTRICT Operator (VA = 4)(0)(0)(0)(0)(11)
| |
| | |MERGE JOIN Operator (Join Type: Left Outer Join) (VA =
3)
| | | Using Worktable2 for internal storage.
| | | Key Count: 1
| | | Key Ordering: ASC
| | |
| | | |SORT Operator (VA = 1)
| | | | Average Row width is 22.000000
| | | | Using Worktable1 for internal storage.
| | | |
| | | | |SCAN Operator (VA = 0)
| | | | | FROM TABLE
| | | | | first
| | | | | a
| | | | | Using Clustered Index.
| | | | | Index : first_pkc_1
| | | | | Forward Scan.
| | | | | Positioning by key.
| | | | | Keys are:
| | | | | one_id ASC
| | | | | two_id ASC
| | | | | date ASC
| | | | | Using I/O Size 16 Kbytes for data pages.
| | | | | With MRU Buffer Replacement Strategy for
data pages.
| | |
| | | |SCAN Operator (VA = 2)
| | | | FROM TABLE
| | | | second
| | | | b
| | | | Using Clustered Index.
| | | | Index : XPKsecone
| | | | Forward Scan.
| | | | Positioning at index start.
| | | | Index contains all needed columns. Base table
will not be read.
| | | | Using I/O Size 16 Kbytes for index leaf pages.
| | | | With LRU Buffer Replacement Strategy for index
leaf pages.
Total estimated I/O cost for statement 1 (at line 1): 77052.
The first table is filled with about 300 million records and the
second table is filled with about 2 million records.
According to the show plan, we it is using work tables and what do
work tables mean?
"Using Worktable2 for internal storage." and "Using Worktable1 for
internal storage."
Is there anything I can do to get rid of the work tables?