By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,813 Members | 1,253 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,813 IT Pros & Developers. It's quick & easy.

outer join and indexes

P: n/a
Hello,
I have query joining several tables, the last table is joined with LEFT
JOIN. The last table
has more then million rows and execution plan shows table scan on it. I have
indexed columns
on which the join is made. If I replace LEFT JOIN with INNER JOIN, index is
used and execution
takes few seconds but with LEFT JOIN there is a table scan , so the
execution
takes several minutes. Does using outer joins turn off indexes? Missed I
something?
What is the reason for such behavior?
(I use SQL Server 2000 Developer edition SP3)

Any suggestion appretiated

eXavier
Jul 20 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
If your using a LEFT JOIN then you just told SQL Server to return every row
from the table on the left regardless of if it has a match on the right or
not. In that case it makes no sense to use the index when it must read all
the rows anyway.

--

Andrew J. Kelly
SQL Server MVP
"eXavier" <hu***@email.cz> wrote in message
news:bi***********@ns.felk.cvut.cz...
Hello,
I have query joining several tables, the last table is joined with LEFT
JOIN. The last table
has more then million rows and execution plan shows table scan on it. I have indexed columns
on which the join is made. If I replace LEFT JOIN with INNER JOIN, index is used and execution
takes few seconds but with LEFT JOIN there is a table scan , so the
execution
takes several minutes. Does using outer joins turn off indexes? Missed I
something?
What is the reason for such behavior?
(I use SQL Server 2000 Developer edition SP3)

Any suggestion appretiated

eXavier

Jul 20 '05 #2

P: n/a
[posted and mailed, please reply in news]

eXavier (hu***@email.cz) writes:
I have query joining several tables, the last table is joined with LEFT
JOIN. The last table has more then million rows and execution plan shows
table scan on it. I have indexed columns on which the join is made. If I
replace LEFT JOIN with INNER JOIN, index is used and execution takes few
seconds but with LEFT JOIN there is a table scan , so the execution
takes several minutes. Does using outer joins turn off indexes? Missed I
something?


I think Andrew misunderstood your question. But maybe I misunderstood
Andrew. I believe this query is akin to the one you have problem with:

select *
from Customers c
left JOIN Orders o ON o.CustomerID = c.CustomerID
where c.PostalCode = '75012'

If you run this in Query Analyzer, with Show Execution Plan on, you will
see that SQL Server uses an Index Seek on Orders.

Really why the optimizer switches to table scan when you use a left
join, I cannot answer, as I don't know the tables and the indexes.

However, it is worth to keep in mind that is not always the best
strategy to use an index. Retrieval by non-clustered index, requires
SQL Server to do bookmark lookups to get the data, and this is only
effecient if you read a smaller portion of the table. At some level
table scan becomes cheaper.

When you are joining this also applies to clustered indexes. SQL
Server can use the clustered index for a nested loop join, but that
is akin to a bookmark lookup, so merge join or a hash when you scan
the table once may be better.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

P: n/a
It's possible I misunderstood his question but he did not provide an actual
query and he mentioned nothing of a WHERE clause. So if he uses an INNER
JOIN against a table on the right that is very small it may indeed use an
index. If he uses a LEFT JOIN with no WHERE clause it doesn't matter how big
or small the right table is, it will do a table scan unless there is an
index that is covering.

--

Andrew J. Kelly
SQL Server MVP
"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn**********************@127.0.0.1...
[posted and mailed, please reply in news]

eXavier (hu***@email.cz) writes:
I have query joining several tables, the last table is joined with LEFT
JOIN. The last table has more then million rows and execution plan shows
table scan on it. I have indexed columns on which the join is made. If I
replace LEFT JOIN with INNER JOIN, index is used and execution takes few
seconds but with LEFT JOIN there is a table scan , so the execution
takes several minutes. Does using outer joins turn off indexes? Missed I
something?


I think Andrew misunderstood your question. But maybe I misunderstood
Andrew. I believe this query is akin to the one you have problem with:

select *
from Customers c
left JOIN Orders o ON o.CustomerID = c.CustomerID
where c.PostalCode = '75012'

If you run this in Query Analyzer, with Show Execution Plan on, you will
see that SQL Server uses an Index Seek on Orders.

Really why the optimizer switches to table scan when you use a left
join, I cannot answer, as I don't know the tables and the indexes.

However, it is worth to keep in mind that is not always the best
strategy to use an index. Retrieval by non-clustered index, requires
SQL Server to do bookmark lookups to get the data, and this is only
effecient if you read a smaller portion of the table. At some level
table scan becomes cheaper.

When you are joining this also applies to clustered indexes. SQL
Server can use the clustered index for a nested loop join, but that
is akin to a bookmark lookup, so merge join or a hash when you scan
the table once may be better.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #4

P: n/a
Andrew J. Kelly (sq************@shadhawk.com) writes:
It's possible I misunderstood his question but he did not provide an
actual query and he mentioned nothing of a WHERE clause. So if he uses
an INNER JOIN against a table on the right that is very small it may
indeed use an index. If he uses a LEFT JOIN with no WHERE clause it
doesn't matter how big or small the right table is, it will do a table
scan unless there is an index that is covering.


Yes, this will scan:

SELECT *
FROM bigtbl b
LEFT JOIN smalltbl s ON b.col = s.col

But I understood his question as that he had:

SELECT *
FROM unknowntbl u1
JOIN unknowntbl u2 ON ...
LEFT JOIN bigtbl b ON ...

And this could well use an index on bigtbl. (With some luck.)

But you are right that the question did not give much information, and
also he did in fact say that he had one small table, but that he had
several tables of unknown size. All we know is that the last one was big.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.