 |
 |
Archives of the TeradataForum
Message Posted: Mon, 10 Apr 2006 @ 19:16:47 GMT
| Subj: | | Re: Implementing Soft RI |
| |
| From: | | Anomy Anom |
<-- Anonymously Posted: Monday, April 10, 2006 15:16 -->
See SQL reference DDL for TTU8.0 page 439 under reference constraint of create table topic.
Example provided there:
Chapter 1: SQL Data Definition Language Statement Syntax
CREATE TABLE (Column Definition Clause)
SQL Reference: Data Definition Statements pages: 439 - 441
Example of How the Optimizer Takes Advantage of Referential Constraints To understand how the Optimizer can produce more performant query plans
when a non-enforced Referential Constraint is defined, consider the following table definitions. The only difference between the second and third
tables is that tb2 defines a Referential Constraint on column b referencing column a in tb1, while tb3 does not define that constraint.
CREATE TABLE tb1 (
a INTEGER NOT NULL PRIMARY KEY,
b INTEGER,
c INTEGER);
CREATE TABLE tb2 (
a INTEGER NOT NULL,
b INTEGER,
c INTEGER,
CONSTRAINT ref1
FOREIGN KEY (b) REFERENCES WITH NO CHECK OPTION tb1(a));
CREATE TABLE tb3 (
a INTEGER NOT NULL,
b INTEGER,
c INTEGER);
The following EXPLAIN report shows the plan for a query on tables tb1 and tb3 when no Referential Constraints have been defined. In
particular, notice the join in Step 5.
EXPLAIN SELECT tb1.a, tb3.a, MAX(tb3.c)
FROM tb1, tb3
GROUP BY tb1.a, tb3.a
WHERE tb1.a = tb3.b
ORDER BY 1;
*** Help information returned. 33 rows.
*** Total elapsed time was 1 second.
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock a distinct TESTCOST."pseudo table" for read on a RowHash to prevent global deadlock for TESTCOST.tb3.
| |
| | 2) | Next, we lock a distinct TESTCOST."pseudo table" for read on a RowHash to prevent global deadlock for TESTCOST.tb1.
| |
| | 3) | We lock TESTCOST.tb3 for read, and we lock TESTCOST.tb1 for read.
| |
| | 4) | We do an all-AMPs RETRIEVE step from TESTCOST.tb3 by way of an all-rows scan with a condition of ("NOT (TESTCOST.tb3.b IS NULL)") into
Spool 4 (all_amps), which is redistributed by hash code to all AMPs. Then we do a SORT to order SORT to order Spool 4 by row hash. The size of
Spool 4 is estimated with no confidence to be 3 rows. The estimated time for this step is 0.03 seconds.
| |
| | 5) | We do an all-AMPs JOIN step from TESTCOST.tb1 by way of a RowHash match scan with no residual conditions, which is joined to Spool 4 (Last
Use). TESTCOST.tb1 and Spool 4 are joined using a merge join, with a join condition of ("TESTCOST.tb1.a = b"). The result goes into Spool 3
(all_amps), which is built locally on the AMPs. The size of Spool 3 is estimated with no confidence to be 3 rows. The estimated time for this
step is 0.20 seconds.
| |
| | 6) | We do a SUM step to aggregate from Spool 3 (Last Use) by way of an all-rows scan, and the grouping identifier in field 1. Aggregate
Intermediate Results are computed locally, then placed in Spool 5. The size of Spool 5 is estimated with low confidence to be 3 rows. The
estimated time for this step is 0.17 seconds.
| |
| | 7) | We do an all-AMPs RETRIEVE step from Spool 5 (Last Use) by way of an all-rows scan into Spool 1 (group_amps), which is built locally on the
AMPs. Then we do a SORT to order SORT to order Spool 1 by the sort key in spool field1. The size of Spool 1 is estimated with low confidence to be
3 rows. The estimated time for this step is 0.17 seconds.
| |
| | 8) | Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
| |
The following EXPLAIN shows the query plan for the same query, but this time on tables tb1 and tb2, with a Referential Constraint defined
between column b of table tb2 and column a of table tb1. Notice that the optimizer has recognized that the join in step 5 of the previous query
plan is unnecessary and can be eliminated. The same query plan would be produced if tb1.a and tb2.b had an explicitly declared standard
Referential Integrity constraint.
EXPLAIN SELECT tb1.a, tb2.a, MAX(tb2.c)
FROM tb1, tb2
GROUP BY tb1.a, tb2.a
WHERE tb1.a = tb2.b
ORDER BY 1;
*** Help information returned. 19 rows.
*** Total elapsed time was 1 second.
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock a distinct TESTCOST."pseudo table" for read on a RowHash to prevent global deadlock for TESTCOST.tb2.
| |
| | 2) | Next, we lock TESTCOST.tb2 for read.
| |
| | 3) | We do a SUM step to aggregate from TESTCOST.tb2 by way of an all-rows scan with a condition of ("NOT (TESTCOST.tb2.b IS NULL)"), and the
grouping identifier in field 1. Aggregate Intermediate Results are computed locally, then placed in Spool 3. The size of Spool 3 is estimated with
no confidence to be 3 rows. The estimated time for this step is 0.15 seconds.
| |
| | 4) | We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of an all-rows scan into Spool 1 (group_amps), which is built locally on the
AMPs. Then we do a SORT to order SORT to order Spool 1 by the sort key in spool field1. The size of Spool 1 is estimated with no confidence to be
3 rows. The estimated time for this step is 0.17 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
| |
| |
