Left outer join on two columns performance issue

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, col2FROM table1, table2WHERE 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).

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, col2FROM table1LEFT OUTER JOIN table2ON table1.person_uid = table2.person_uidWHERE 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.

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