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

Do you have covering indexes on person_uid and period for both tables?

If not, add them and try again.

Take a look at the execution plan and see what the query is actually doing.

Also: What are the datatypes of the fields? Are they the same in both tables? An implicit cast can really slow things down.

Answer 2

Do these tables have indexes on the columns you're joining? Install Oracle's free SQLDeveloper product and use it to do an "explain" on that query and see if it's doing sequential scans of both tables.

Answer 3

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).

Answer 4

I think you need to understand why the last two are not the same query as the first one. If you do a left join and then add a where clause referncing a field in the table on the right side of the join (the one which may not always have a record to match the first table), then you have effectively changed the join to an inner join. There is one exception to this and that is if you reference something like

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

In this case you asking for the record which don't have a record in the second table. But other than this special case, you are changing the left join to an inner join if you refence a field in table2 in the where clause.

If your query is not fast enough, I would look at your indexing.

Answer 5

In a left join you'd be scanning table1 for each unique combination of (person_uid,period) then searching table2 for all corresponding records there. If table2 doesn't have an appropriate index, this can involve scanning the whole of that table too.

My best guess, without seeing an execution plan, is that the first query (the only one which seems to be correct) is having to table scan table2 as well as table1.

As you say that you can't change the indexes, you need to change the query. As far as I can tell, there is only one realistic alternative...

SELECT
   col1, col2
FROM
   table2
FULL OUTER JOIN
   table1
      ON table1.person_uid = table2.person_uid
      AND table1.period = table2.period
WHERE
   table1.person_uid IS NOT NULL

The hope here is that you scan table2 for each unique combination of (person_uid, period), but make use of indexes on table1. (As opposed to scanning table1 and making use of indexes on table2, which what I expected from your query.)

If table1 doesn't have appropriate indexes, however, you'll be very unlikely to see any performance improvement at all...

Dems.

Answer 6

Anything anyone tells you based on the information you provided is a guess.

Look at the execution plan for the query. If you don't see a reason for the slowness in the plan, the post the plan here.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/ex_plan.htm#PFGRF009