I have the following contrived tables
car (id char(8) NOT NULL, make char(10) NOT NULL)
with NONCLUSTERED INDEX i0 ON car(make)
~80k rows of which ~2k are makes with code I need
carmake (make char(10) NOT NULL, code int NOT NULL)
with NONCLUSTERED INDEX i1 ON carmake(make)
~2k rows of which 2 have the code I want
So I want all the cars from car that have a carmake.code = 123
If I do this...I get the detailed plan below
select id FROM car c JOIN carmake m ON c.make = m.make where m.code = 123
Note that it table scans the large table - urgh!
If I do either of these...the plan shows that it uses the i0 index
select id from car where code ='make1' OR code ='make2'
select id from car where code in('make1','make2')
As soon as you join to the make table to derive the makes based on the code, the query plan starts creating a reformatted worktable and then subsequently table scans the big table.
I can't figure out why. The sybase documentation states that reformatting occurs when there isn't a suitable key between the tables to join. In this case both tables have make char(10) indexes. I've also tried adding an index on code so that make isn't table scanned but this still results in the initial reformatting. I suspect the reformatting causes the inability to use the car index - maybe because of a type clash and an implcit conversion? But why is the formatting happening in the first place?
Plan with reformatting then table scan: -
QUERY PLAN FOR STATEMENT 1 (at line 1).
Executed in parallel by coordinating process and 3 worker processes.
STEP 1
The type of query is INSERT.
The update mode is direct.
Executed by coordinating process.
Worktable1 created for REFORMATTING.
FROM TABLE
make
m
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.
STEP 2
The type of query is SELECT.
Executed in parallel by coordinating process and 3 worker processes.
FROM TABLE
car
c
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Executed in parallel with a 3-way hash scan.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
Worktable1.
Nested iteration.
Using Clustered Index.
Forward scan.
Positioning by key.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
Parallel network buffer merge.
The sort for Worktable1 is done in Serial
