Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 13 May 2008 @ 10:59:30 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: View with Default and Null values
 
From:   Parija, Soumya Ranjan

Hi all,

I tried this scenario on the latest dbs and got the result like below. I want your comments on this.

     show table TABLE1;

       *** Text of DDL statement returned.
       *** Total elapsed time was 1 second.

     -------------------------------------------------------------
     CREATE SET TABLE SOUMYA.TABLE1 ,NO FALLBACK ,
           NO BEFORE JOURNAL,
           NO AFTER JOURNAL,
           CHECKSUM = DEFAULT
           (
            column1 INTEGER)
     PRIMARY INDEX ( column1 );


       BTEQ -- Enter your DBC/SQL request or BTEQ command:
     sel * from TABLE1;

     sel * from TABLE1;

       *** Query completed. 3 rows found. One column returned.
       *** Total elapsed time was 1 second.

          column1
     -----------
                3
                1
                2

       BTEQ -- Enter your DBC/SQL request or BTEQ command:
     show table TABLE2;

     show table TABLE2;

       *** Text of DDL statement returned.
       *** Total elapsed time was 1 second.

     -------------------------------------------------------------
     CREATE SET TABLE SOUMYA.TABLE2 ,NO FALLBACK ,
           NO BEFORE JOURNAL,
           NO AFTER JOURNAL,
           CHECKSUM = DEFAULT
           (
            column1 INTEGER,
            column2 VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC)
     PRIMARY INDEX ( column1 );


       BTEQ -- Enter your DBC/SQL request or BTEQ command:
     sel * from TABLE2;

     sel * from TABLE2;

       *** Query completed. One row found. 2 columns returned.
       *** Total elapsed time was 1 second.

          column1  column2
     -----------  ------------------------------
                1  One

       BTEQ -- Enter your DBC/SQL request or BTEQ command:
     show view VIEW_tbl2;

     show view VIEW_tbl2;

       *** Text of DDL statement returned.
       *** Total elapsed time was 1 second.

     -------------------------------------------------------------
     CREATE VIEW VIEW_tbl2
     AS
     SELECT
           column1,
           'Default' Column2
     FROM
     TABLE2;

       BTEQ -- Enter your DBC/SQL request or BTEQ command:
     sel * from VIEW_tbl2;

     sel * from VIEW_tbl2;

       *** Query completed. One row found. 2 columns returned.
       *** Total elapsed time was 1 second.

          column1  Column2
     -----------  -------
                1  Default

       BTEQ -- Enter your DBC/SQL request or BTEQ command:
     SELECT TB2.column2, count (*)
     FROM TABLE1 TB1 left outer join VIEW_tbl2 TB2 on TB1.column1 =
     TB2.column1 Group By 1;

     SELECT TB2.column2, count (*)
     FROM TABLE1 TB1 left outer join VIEW_tbl2 TB2 on TB1.column1 =
     TB2.column1
       Group By 1;

       *** Query completed. 2 rows found. 2 columns returned.
       *** Total elapsed time was 1 second.

     Column2     Count(*)
     -------  -----------
     Default            1
     ?                  2

       BTEQ -- Enter your DBC/SQL request or BTEQ command:
     explain SELECT TB2.column2, count (*)
     FROM TABLE1 TB1 left outer join VIEW_tbl2 TB2 on TB1.column1 =
     TB2.column1 Group By 1;

     explain SELECT TB2.column2, count (*)
     FROM TABLE1 TB1 left outer join VIEW_tbl2 TB2 on TB1.column1 =
     TB2.column1
       Group By 1;

       *** Help information returned. 31 rows.
       *** Total elapsed time was 1 second.

     Explanation
     -------------------------------------------------------------
        1) First, we lock a distinct SOUMYA."pseudo table" for read on a
           RowHash to prevent global deadlock for SOUMYA.TABLE2.
        2) Next, we lock a distinct SOUMYA."pseudo table" for read on a
           RowHash to prevent global deadlock for SOUMYA.TB1.
        3) We lock SOUMYA.TABLE2 in view VIEW_tbl2 for read, and we lock
           SOUMYA.TB1 for read.
        4) We do an all-AMPs JOIN step from SOUMYA.TB1 by way of a RowHash
           match scan with no residual conditions, which is joined to
           SOUMYA.TABLE2 in view VIEW_tbl2 by way of a RowHash match scan
           with no residual conditions.  SOUMYA.TB1 and SOUMYA.TABLE2 are
           left outer joined using a merge join, with a join condition of (
           "SOUMYA.TB1.column1 = SOUMYA.TABLE2.column1").  The result goes
           into Spool 4 (all_amps), which is built locally on the AMPs.  The
           size of Spool 4 is estimated with low confidence to be 16 rows (
           400 bytes).  The estimated time for this step is 0.09 seconds.
        5) We do an all-AMPs SUM step to aggregate from Spool 4 (Last Use) by
           way of an all-rows scan , grouping by field1 ( (CASE WHEN
           (SOUMYA.TABLE2.ROWID IS NULL) THEN (NULL) ELSE ('Default') END)).
           Aggregate Intermediate Results are computed globally, then placed
           in Spool 5.  The size of Spool 5 is estimated with low confidence
           to be 2 rows (70 bytes).  The estimated time for this step is 0.11
           seconds.
        6) We do an all-AMPs RETRIEVE step from Spool 5 (Last Use) by way of
           an all-rows scan into Spool 2 (group_amps), which is built locally
           on the AMPs.  The size of Spool 2 is estimated with low confidence
           to be 2 rows (78 bytes).  The estimated time for this step is 0.08
           seconds.
        7) Finally, we send out an END TRANSACTION step to all AMPs involved
           in processing the request.
        -> The contents of Spool 2 are sent back to the user as the result of
           statement 1.  The total estimated time is 0.28 seconds.

     Select *
     from TABLE1 TB1
        left outer join
     VIEW_tbl2 TB2 on
     TB1.Column1 = TB2.Column1
     where TB2.Column2 is NULL;

     Select *
     from TABLE1 TB1
        left outer join
     VIEW_tbl2 TB2 on
     TB1.Column1 = TB2.Column1
     where TB2.Column2 is NULL;

       *** Query completed. 2 rows found. 3 columns returned.
       *** Total elapsed time was 1 second.

          column1      column1  Column2
     -----------  -----------  -------
                3            ?  ?
                2            ?  ?

       BTEQ -- Enter your DBC/SQL request or BTEQ command:
     explain Select *
     from TABLE1 TB1
        left outer join
     VIEW_tbl2 TB2 on
     TB1.Column1 = TB2.Column1
     where TB2.Column2 is NULL;

     explain Select *
     from TABLE1 TB1
        left outer join
     VIEW_tbl2 TB2 on
     TB1.Column1 = TB2.Column1
     where TB2.Column2 is NULL;

       *** Help information returned. 25 rows.
       *** Total elapsed time was 1 second.

     Explanation
     -------------------------------------------------------------
        1) First, we lock a distinct SOUMYA."pseudo table" for read on a
           RowHash to prevent global deadlock for SOUMYA.TABLE2.
        2) Next, we lock a distinct SOUMYA."pseudo table" for read on a
           RowHash to prevent global deadlock for SOUMYA.TB1.
        3) We lock SOUMYA.TABLE2 in view VIEW_tbl2 for read, and we lock
           SOUMYA.TB1 for read.
        4) We do an all-AMPs JOIN step from SOUMYA.TB1 by way of a RowHash
           match scan with no residual conditions, which is joined to
           SOUMYA.TABLE2 in view VIEW_tbl2 by way of a RowHash match scan
           with no residual conditions.  SOUMYA.TB1 and SOUMYA.TABLE2 are
           left outer joined using a merge join, with a join condition of (
           "SOUMYA.TB1.column1 = SOUMYA.TABLE2.column1").  The result goes
           into Spool 3 (all_amps), which is built locally on the AMPs.  The
           size of Spool 3 is estimated with low confidence to be 16 rows (
           496 bytes).  The estimated time for this step is 0.04 seconds.
        5) 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 (Field_2 IS
           NULL) THEN (NULL) ELSE ('Default') END )IS NULL") into Spool 2
           (group_amps), which is built locally on the AMPs.  The size of
           Spool 2 is estimated with low confidence to be 16 rows (800 bytes).
           The estimated time for this step is 0.08 seconds.
        6) Finally, we send out an END TRANSACTION step to all AMPs involved
           in processing the request.
        -> The contents of Spool 2 are sent back to the user as the result of
           statement 1.  The total estimated time is 0.12 seconds.

       BTEQ -- Enter your DBC/SQL request or BTEQ command:

Thanks,

Soumya



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016