Partially Qualifying Join Index

Database
Teradata Employee

Partially Qualifying Join Index

Hello All,

While covering the Join Index in Database Design PDF, I came across this section explaining the conditions for partially covering join index to prevail:

(a) RowID of the underlying tables.

(b) UPI of the base tables

(c) NUPI [If RowID or USI] included.

In the folloing example:

CREATE TABLE t1 (
a1 INTEGER,
b1 INTEGER,
c1 INTEGER);

CREATE TABLE t2 (
a2 INTEGER,
b2 INTEGER,
c2 INTEGER);

CREATE TABLE t3 (
a3 INTEGER,
b3 INTEGER,
c3 INTEGER);

CREATE JOIN INDEX j1 AS
SELECT b1, b2, t1.ROWID AS t1rowid
FROM t1,t2
WHERE a1=a2;

CREATE JOIN INDEX j2 AS
SELECT b1, b2, t1.ROWID t1rowid, t2.rowid t2rowid
FROM t1,t2
WHERE a1=b2;

Join index j1 partially covers the following queries:

SELECT a1, b1, c1, b2
FROM t1,t2
WHERE t1.a1=t2.b2
AND t1.b1=10;
SELECT a1, b1, c1, b2, b3, c3
FROM t1,t2,t3
WHERE t1.a1=t2.b2
AND t1.b1=t3.a3
AND t3.b3 > 0;

The same join index does not partially cover the following queries:

SELECT *
FROM t1,t2
WHERE t1.a1=t2.b2
AND t1.b1=10;
SELECT *
FROM t1,t2,t3
WHERE t1.a1=t2.b2
AND t1.b1=t3.a3
AND t3.b3 > 0;

Join index j2, on the other hand, partially covers all of these queries.

Why J1 covers the top 2 select queries and J2 covers all ? Maybe I am not relating the conditions to the facts properly. 

Thanks in advance,

Smarak