Left outer join help

Database
Junior Contributor

Re: Left outer join help

@Mahs

Btw, it's not an Outer Join, the optimizer replaced with an Inner :-)

And for Inner Joins there's no difference between placing a filter in ON or WHERE.

Dieter

Re: Left outer join help

Thanks dnoeth.

I am talking about query based on view. Sorry , here is the query :

Yes the explains are different.

on V2R6.

select

A.x, A.y,

B.y, B.DEL_FLG

FROM

sr.t1 AS A

LEFT OUTER JOIN sr.v_t2 AS B

ON ( A.y=B.y ) WHERE

B.DEL_FLG<>'D';

 *** Query completed. One row found. 4 columns returned.

 *** Total elapsed time was 1 second.

x        y        y        DEL_FLG

-------  -------  -------  -------

1234567  1234     ?        0

 4) We do an all-AMPs RETRIEVE step from sr.A by way of an all-rows

    scan with no residual conditions into Spool 2 (all_amps), which is

    redistributed by hash code to all AMPs.  Then we do a SORT to

    order Spool 2 by row hash.  The size of Spool 2 is estimated with

    low confidence to be 4 rows.  The estimated time for this step is

    0.01 seconds.

 5) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of a

    RowHash match scan, which is joined to sr.t2 by way of a RowHash

    match scan.  Spool 2 and sr.t2 are left outer joined using a merge

    join, with a join condition of ("y = sr.t2.y").  The result goes

    into Spool 3 (all_amps), which is built locally on the AMPs.  The

    size of Spool 3 is estimated with index join confidence to be 8

    rows.  The estimated time for this step is 0.04 seconds.

 6) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of

    an all-rows scan with a condition of ("(( CASE WHEN (z <

    '20110530') THEN ('D') ELSE ('0') END ))<> 'D'") into Spool 1

    (group_amps), which is built locally on the AMPs.  The size of

    Spool 1 is estimated with index join confidence to be 8 rows.  The

    estimated time for this step is 0.07 seconds.

on TD12:

select

A.x, A.y,

B.y, B.DEL_FLG

FROM

sr.t1 AS A

LEFT OUTER JOIN sr.v_t2 AS B

ON ( A.y=B.y ) WHERE

B.DEL_FLG<>'D';

 *** Query completed. No rows found.

 *** Total elapsed time was 1 second.

4) We do an all-AMPs RETRIEVE step from sr.A by way of an all-rows

   scan with a condition of ("NOT (sr.A.y IS NULL)") into Spool 3

   (all_amps), which is redistributed by the hash code of (sr.A.y) to

   all AMPs.  Then we do a SORT to order Spool 3 by row hash.  The

   size of Spool 3 is estimated with no confidence to be 2 rows (54

   bytes).  The estimated time for this step is 0.01 seconds.

5) We do an all-AMPs JOIN step from sr.t2 in view v_t2 by way of a

   RowHash match scan with a condition of ("(( CASE WHEN (sr.t2 in

   view v_t2.z < '20110530') THEN ('D') ELSE ('0') END ))<> 'D'"),

   which is joined to Spool 3 (Last Use) by way of a RowHash match

   scan.  sr.t2 and Spool 3 are joined using a merge join, with a

   join condition of ("y = sr.t2.y").  The result goes into Spool 2

   (group_amps), which is built locally on the AMPs.  The size of

   Spool 2 is estimated with no confidence to be 2 rows (86 bytes).

   The estimated time for this step is 0.05 seconds.

Enthusiast

Re: Left outer join help

Hi Dieter,

I ran this query using a test table created and try to see whether any inner joins are present in explain plan or not, but I could not find them. Possibly I am reading the explain plan incorrectly. I am fairly new to teradata with out much hands on the SQL off late. can you please let me know where i am missing.. 

EXPLAIN SELECT a.Emp_no,a.Emp_name,b.Emp_no,b.status FROM

Outer_table a LEFT JOIN Inner_table b ON a.Emp_no=b.Emp_no

WHERE

b.status<>'UNKNOWN'

ORDER BY 1;

1) FIRST, we LOCK a DISTINCT "pseudo table"

FOR READ ON a RowHash TO prevent GLOBAL deadlock FOR

b.

2) NEXT, we LOCK a DISTINCT "pseudo table"

FOR READ ON a RowHash TO prevent GLOBAL deadlock FOR

a.

3) We LOCK b FOR READ, AND we LOCK

a FOR READ.

4) We DO an ALL-AMPs JOIN step FROM b BY

way OF a RowHash match scan WITH a CONDITION OF (

"b.status <> 'UNKNOWN'"), which IS

joined TO a BY way OF a RowHash match

scan WITH NO residual conditions. b

AND a are joined USING a MERGE JOIN,

WITH a JOIN CONDITION OF ("a.EMP_NO =

b.EMP_NO"). The RESULT goes INTO SPOOL

1 (all_amps), which IS built locally ON the AMPs. THEN we DO a

SORT TO ORDER SPOOL 1 BY the sort KEY IN SPOOL field1 (

a.EMP_NO). The SIZE OF SPOOL 1 IS

estimated WITH NO confidence TO be 692 ROWS (36,676 BYTES). The

estimated TIME FOR this step IS 0.04 seconds.

5) Finally, we send OUT an END TRANSACTION step TO ALL AMPs involved

IN processing the REQUEST.

-> The contents OF SPOOL 1 are sent back TO the USER AS the RESULT OF

STATEMENT 1. The total estimated TIME IS 0.04 seconds.

Enthusiast

Re: Left outer join help

@Pveedu:  You can see some thing like "b


AND a are joined USING a MERGE JOIN,


WITH a JOIN CONDITION OF ("a.EMP_NO =


b.EMP_NO")." in 4th point. Merge join is what inner join here. Hope it clears your doubt.

Enthusiast

Re: Left outer join help

Yes.. Thank You... I appreciate it..

Highlighted
Enthusiast

Re: Left outer join help

outer joins can have a butterfly effect & unintended consequences.

The Rolf inspired case study that Dieter refernced is a good one and can be found at this link:

http://www.teradataforum.com/teradata_pdf/b035-1101-061a_6.pdf

Thanks to Dieter and Rolf.

Teradata Employee

Re: Left outer join help

Hi all,

 is the above mentioned article (the cas study that Dieter was referencing) up for grabs ? 

 I have lost it through reinstallation of my computer and cannot find it anymore/anywhere.

 Thanks a lot !!

 michal

Junior Contributor

Re: Left outer join help

This is probably the "Outer Join Case Study" found in the SQL DML manual:

http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/SQL_Reference/B035_1146_111A/ch02.033.077.html