Database Tuning (MOSC)

MOSC Banner

SQL Tuning Left Outer Join

edited Jul 22, 2012 7:35PM in Database Tuning (MOSC) 4 commentsAnswered
The below query with left outer join runs for more 6 hours and does not complete in production.  The stats are all up to date.  

When I changed the left outer join to a inner join the query comes back in few minutes. Please help me tune the query and why does left outer join take so long to run?

SQL> explain plan for 
select    /* + ordered use_hash  (a, b) */
 a.case_closed_dt, a.acct_id, a.activity_id,  substr(a.rep_user_id,1,5) as rep_user_id, a.cnctn_id, a.contact_method, a.acct_num_ent,b.contactor_type, b.view_type 

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center