Hashmatch with left Outer Join

Hi,

I am joining two tables on the clumns which are covered in non clustered index. Both the tables have clustered index.

When i make left outer join then it performs hashmatch operation and when i make inner join then it performs nested loop.

I am executing the query on a test database which hardly used to go under any change and by this i guess there will not be any problem with statistics.

I did not try rebuilding the index but i dont think so there is a problem with index.
I dont know why it is happening and on top of it am i making any sense in this observation.
Any idea/suggestion would be of great help.
Thanks

Hi,
This seems to be an interesting issue.. Can you post the table structure along with indexes so that we can try and figure out whats going on?

Table1: CCLVST
(
CLID NUMERIC (16,0)
CLGID NUMERIC (16,0),
CLCHID NUMERIC (16,0),
CLName Varchar (100)
-
-
-
)

Index:
NC Index 1 (CLCHID)
C Index 1 (CLGID, CLID)
NC Index 2 (CLID)

Table2: CLPHYNT
(
CLPHID NUMERIC (16,0),
CLGID NUMERIC (16,0),
CLCHID NUMERIC (16,0),
PHYNT VARCHAR(100),
)

Index:
NC Index 1 (CLPHID)
C Index 1 (CLGID, CLCHID)

SELECT C.CLName, P.PHYNT
FROM
CCLVST C LEFT OUTER JOIN CLPHYNT P ON
C.CLGID = P.CLGID AND
C.CLCHID = P.CLCHID

Please check this if it makes any sense.
Thanks

An inner join is usually a more 'economical' operation, in that the database engine only needs to consider matching rows, and can safely ignore all others.
Why does your query need an outer join? If you are deleting rows for which there is no matching row on another table, then you might try using a NOT EXISTS correlated subquery in the WHERE clause, instead of the outer join.

Why i am user left outer join because it is my requirement to display the data in reports. visit table maintains all visits and note table maintains some 20 attributes per visit. It may be possible that every visit does not record the data for all 20 notes. So need to display all notes per visit whether entered or not.

I think it is happening because of difference in number of rows in both the tables. Per user, visit table can have n number of visits and each visit, need to enter attributes.

note table has roughly 10 times data as of visit table. But this is a problem for as it is scanning more rows.

May be you can comment on Index structure as visit table has different index structure than that of note table for two columns used in join. One has both the columns in same index and other has two different index for two different columns.
Thanks

Sorry for the delay in replying. (I started working on it but got carried away with other thigns
There were two issues I see:
1. The rows should be quite high on CLPHYNT
2. The index differences cause the first table to give the data in different order.
My suggestion is the have a non clustered index on CCLVST on columns CLGID and CLCHID with an include of CLName.
This suggestion may make your inserts, updates and deletes to be slower if you have many such activities on CCLVST