Exec plan with outer join

forum.sqlbase (2000-2010)
NewsgroupServer
Robot
Robot
Posts: 115421
Joined: 24 Feb 2017, 12:00
Location: World wide

Exec plan with outer join

Post by NewsgroupServer » 25 Sep 2009, 09:49

 Posted by:  F R Bhote 

When I do a join on 3 tables - on of which is an outer join, then the table
with the outer join is the last to be executed. For instance:

B_PARTY = T_PARTY and S_CO = CO_CODE on tables BILLS, TRADES, SCRIPS gives :

OUTER TBL INDEX USED-O INNER TBL INDEX USED-I RESULT TBL
BILLS XB_PARTY TRADES XT_BILL
TMP11
TMP11 XT_BILL SCRIPS XSCR_SET
RESULT

which is fine as BILLS has 2 rows, TRADES has 4 rows and SCRIPS has 100,000
rows.

If I add an outer join B_PARTY = T_PARTY(+) and S_CO = CO_CODE

OUTER TBL INDEX USED-O INNER TBL INDEX USED-I RESULT TBL
BILLS XB_PARTY SCRIPS XSCR_SET
TMP11 -> here a join is done with 100,000 rows and takes forever
TMP11 XSCR_SET TRADES XT_BILL
RESULT

How can I force the execution plan of the predicate without the outer join?

Return to “forum.sqlbase”

Who is online

Users browsing this forum: [Ccbot] and 0 guests