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 ASSELECT b1, b2, t1.ROWID AS t1rowidFROM t1,t2WHERE a1=a2;CREATE JOIN INDEX j2 ASSELECT b1, b2, t1.ROWID t1rowid, t2.rowid t2rowidFROM t1,t2WHERE a1=b2;Join index j1 partially covers the following queries:SELECT a1, b1, c1, b2FROM t1,t2WHERE t1.a1=t2.b2AND t1.b1=10;SELECT a1, b1, c1, b2, b3, c3FROM t1,t2,t3WHERE t1.a1=t2.b2AND t1.b1=t3.a3AND t3.b3 > 0;The same join index does not partially cover the following queries:SELECT *FROM t1,t2WHERE t1.a1=t2.b2AND t1.b1=10;SELECT *FROM t1,t2,t3WHERE t1.a1=t2.b2AND t1.b1=t3.a3AND 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,
rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.