Left outer join on two columns performance issue

Question

I'm using a SQL query that is similar to the following form:

SELECT col1, col2
FROM table1
LEFT OUTER JOIN table2
ON table1.person_uid = table2.person_uid
AND table1.period = table2.period

And it's either way too slow or something's deadlocking because it takes at least 4 minutes to return. If I were to change it to this:

SELECT col1, col2
FROM table1
LEFT OUTER JOIN table2
ON table1.person_uid = table2.person_uid
WHERE table1.period = table2.period

then it works fine (albeit not returning the right number of columns). Is there any way to speed this up?

UPDATE: It does the same thing if I switch the last two lines of the latter query:

SELECT col1, col2
FROM table1
LEFT OUTER JOIN table2
ON table1.period = table2.period
WHERE table1.person_uid = table2.person_uid

UPDATE 2: These are actually views that I'm joining. Unfortunately, they're on a database I don't have control over, so I can't (easily) make any changes to the indexing. I am inclined to agree that this is an indexing issue though. I'll wait a little while before accepting an answer in case there's some magical way to tune this query that I don't know about. Otherwise, I'll accept one of the current answers and try to figure out another way to do what I want to do. Thanks for everybody's help so far.

Answer 1

Bear in mind that statements 2 and 3 are different to the first one.

How? Well, you're doing a left outer join and your WHERE clause isn't taking that into account (like the ON clause does). At a minimum, try:

SELECT col1, col2
FROM table1, table2
WHERE table1.person_uid = table2.person_uid (+)
AND table1.period = table2.period (+)

and see if you get the same performance issue.

What indexes do you have on these tables? Is this relationship defined by a foreign key constraint?

What you probably need is a composite index on both person_uid and period (on both tables).