Examples of Exceptions to the General Rules for Extra Tables in the Join Index Definition
“Example 1” on page 541 and “Example 2” on page 542 illustrate cases that are exceptions to the general coverage rules for extra tables in a join index definition (see “Restriction on Coverage by Join Indexes When a Join Index Definition References More Tables Than a Query” on page 528).
Example
In the following example, table t9 is the parent table of tables t7 and t8. Generally, this relationship disqualifies a join index from covering any query with
fewer tables than are referenced in the definition for that index. However, because
t7 and t8 are joined on the FK columns (y7=x8) in the join index definition, the Optimizer uses the index ji to cover the query, as you can see by looking at the bold text in the EXPLAIN report:
CREATE SET TABLE t7(
x7 INTEGER NOT NULL,
y7 INTEGER NOT NULL,
z7 INTEGER NOT NULL,
CONSTRAINT r7 FOREIGN KEY (y7)
REFERENCES WITH NO CHECK OPTION t9 (y9))
PRIMARY INDEX (x7);
CREATE SET TABLE t8(
x8 INTEGER NOT NULL,
y8 INTEGER NOT NULL,
z8 INTEGER NOT NULL,
CONSTRAINT r8 FOREIGN KEY (x8)
REFERENCES WITH NO CHECK OPTION t9 (x9));
CREATE SET TABLE t9(
x9 INTEGER NOT NULL UNIQUE,
y9 INTEGER NOT NULL,
z9 INTEGER NOT NULL)
UNIQUE PRIMARY INDEX(y9);
CREATE JOIN INDEX ji AS
SELECT x7, y7, x8, y8, x9, y9
FROM t7, t8, t9
WHERE y7=x8
AND y7=y9
AND x8=x9;
EXPLAIN SELECT x7, y7, x8, y8
FROM t7, t8
WHERE y7=x8
AND x7>1;
*** Help information returned. 14 rows.
*** Total elapsed time was 1 second.
Explanation
---------------------------------------------------------------------------
1) First, we lock a distinct HONG_JI."pseudo table" for
read on a RowHash to prevent global deadlock for HONG_JI.ji.
2) Next, we lock HONG_JI.ji for read.
3) We do an all-AMPs RETRIEVE step from HONG_JI.ji by way
of an all-rows scan with a condition of (
"HONG_JI.ji.x1 > 1") into Spool 1 (group_amps), which
is built locally on the AMPs. The size of Spool 1 is estimated
with no confidence to be 3 rows. The estimated time for this step
is 0.06 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.06 seconds.
Example
As with “Example 1” on page 541, this example demonstrates how a join index can be used to cover a query when one
table in the join index definition is a parent of two others if the tables are joined
on foreign key‑primary key relationships. t9 is the parent table of both t7 and t10. But because t7 and t10 are joined with t9 on the same PK column, by transitive closure, t7 and t10 are joined on y7=x10. The Optimizer does select join index ji to cover the query, as the bold text in the EXPLAIN report for the example query
demonstrates:
CREATE SET TABLE t10(
x10 INTEGER NOT NULL,
y10 INTEGER NOT NULL,
z10 INTEGER NOT NULL,
CONSTRAINT r10 FOREIGN KEY ( x10 )
REFERENCES WITH NO CHECK OPTION t9 ( y9 ))
PRIMARY INDEX x10;
CREATE JOIN INDEX ji AS
SELECT x7, y7, x10, y10, x9, y9
FROM t7, t10, t9
WHERE y7=y9
AND x10=y9;
EXPLAIN SELECT x7, y7, x10, y10
FROM t7, t10
WHERE y7=x10
AND x7>1;
*** Help information returned. 14 rows.
*** Total elapsed time was 1 second.
Explanation
---------------------------------------------------------------------------
1) First, we lock a distinct HONG_JI."pseudo table" for
read on a RowHash to prevent global deadlock for HONG_JI.ji.
2) Next, we lock HONG_JI.ji for read.
3) We do an all-AMPs RETRIEVE step from HONG_JI.ji by way
of an all-rows scan with a condition of ("HONG_JI.ji.x1 > 1")
into Spool 1 (group_amps), which is built locally on the AMPs.
The size of Spool 1 is estimated with no confidence to be 3 rows.
The estimated time for this step is 0.06 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.06 seconds.