The following examples show cases that are exceptions to the general coverage rules for extra tables in a join index definition.
Example: Table T9 is the Parent Table of Tables T7 and T8.
In the following example, table t9 is the parent table of tables t7 and t8. 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 the bold text in the EXPLAIN report shows:
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 HONG_JI.ji for read on a reserved RowHash to prevent a global deadlock. 2) Next, 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. 3) 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: SELECT Join Index Covers a Query
As with Example: Table T9 is the Parent Table of Tables T7 and T8, 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 HONG_JI.ji for read on a reserved RowHash to prevent a global deadlock. 2) Next, 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. 3) 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.