 |
 |
Archives of the TeradataForum
Message Posted: Wed, 31 Oct 2012 @ 16:49:13 GMT
| Subj: | | Re: Question on soft "RI" in Teradata |
| |
| From: | | Bernard, Vincent L |
The Optimizer is aware of Soft RI. Therefore, is a "parent" entity is referenced by a "child", it is understood that every value in the
"child's" joining column needs to first exist in the parent. It is not enforced by the database, so the developer is responsible for ensuring
it.
Since the relationship is understood, the optimizer can draw conclusions.
For example:
create table parenttable
(
col1 integer,
col2 integer,
col3 integer
)unique primary index(col1)
;
create table childtable
(
col1 integer,
col2 integer,
col3 integer,
col4 integer,
constraint fkey foreign key (col1) references WITH NO CHECK OPTION
parenttable(col1)
)unique primary index (col1,col2)
;
(ParentTable.col1 is referenced by ChildTable.col1)
Query:
SELECT
c.col1,
c.col2,
c.col3,
c.col4
FROM parenttable p inner join childtable c on p.col1 = c.col1 ;
The optimizer will assume that there is no need to join the tables and the explain reflects this.
Explain SELECT
c.col1,
c.col2,
c.col3,
c.col4
FROM parenttable p inner join childtable c on p.col1 = c.col1 ; (Explain with the
constraint)
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock a distinct TESTDB."pseudo table" for read on a RowHash to prevent global deadlock for TESTDB.c.
| |
| | 2) | Next, we lock TESTDB.c for read.
| |
| | 3) | We do an all-AMPs RETRIEVE step from TESTDB.c by way of an all-rows scan with a condition of ("NOT (TESTDB.c.col1 IS NULL)") into Spool 1
(all_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 48 rows (1,776 bytes). The estimated
time for this step is 0.01 seconds.
| |
| | 4) | 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.01 seconds.
| |
(Explain with constraint dropped)
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock a distinct TESTDB."pseudo table" for read on a RowHash to prevent global deadlock for TESTDB.c.
| |
| | 2) | Next, we lock a distinct TESTDB."pseudo table" for read on a RowHash to prevent global deadlock for TESTDB.p.
| |
| | 3) | We lock TESTDB.c for read, and we lock TESTDB.p for read.
| |
| | 4) | We do an all-AMPs RETRIEVE step from TESTDB.c by way of an all-rows scan with a condition of ("NOT (TESTDB.c.col1 IS NULL)") into Spool 2
(all_amps), which is redistributed by the hash code of (TESTDB.c.col1) 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 48 rows (1,392 bytes). 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 TESTDB.p by way of a RowHash match
scan. Spool 2 and TESTDB.p are joined using a merge join, with a join condition of ("TESTDB.p.col1 = col1"). The result goes into Spool 1
(all_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with index join confidence to be 48 rows (1,776 bytes). The
estimated time for this step is 0.02 seconds.
| |
| | 6) | 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.02 seconds.
| |
Vincent Bernard
| |
