 |
 |
Archives of the TeradataForum
Message Posted: Tue, 13 May 2008 @ 10:59:30 GMT
| 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
| |
