Yes sir, I know about " .explain ". Next time I would try that @ Richard Hipp. And thanks Clemens.
On Fri, Apr 10, 2015 at 3:57 PM, Clemens Ladisch <clemens at ladisch.de> wrote: > Sairam Gaddam wrote: > > On Thu, Apr 9, 2015 at 1:04 PM, Clemens Ladisch <clemens at ladisch.de> > wrote: > >> Sairam Gaddam wrote: > >>> sql="create table em(name text primary key,age text,pts text);"\ > >>> "create table l(name text primary key,fame text);"; > >>> > >>> sql = "select * from em,l where l.fame=em.age"; > >>> > >>> 4 Once 0 13 0 00 > >>> 5 OpenAutoindex 2 3 0 k(3,nil,nil,nil) 00 > >>> 6 Rewind 1 13 0 00 > >>> 7 Column 1 1 2 00 > >>> 8 Column 1 0 3 00 > >>> 9 Rowid 1 4 0 00 > >>> 10 MakeRecord 2 3 1 00 > >>> 11 IdxInsert 2 1 0 10 > >>> 12 Next 1 7 0 03 > >>> 13 Column 0 1 5 00 > >>> 14 IsNull 5 24 0 00 > >>> 15 SeekGE 2 24 5 1 00 > >>> 16 IdxGT 2 24 5 1 00 > >>> 17 Column 0 0 6 00 > >>> 18 Copy 5 7 0 00 > >>> 19 Column 0 2 8 00 > >>> 20 Column 2 1 9 00 > >>> 21 Column 2 0 10 00 > >>> 22 ResultRow 6 5 0 00 > >>> 23 Next 2 16 0 00 > >>> 24 Next 0 4 0 01 > >>> ... > >>> > >>> whenever the condition in the where clause is false, the program jumps > to > >>> the instruction pointed by p2 of SeekGe > >> > >> Yes. > >> > >>> but if the condition proves to be false for the row 1 of both the > >>> tables, then the program jumps to line 24(in this case) which > >>> corresponds to outer table and takes the second row of outer table > >>> for next iteration, then when will the program fetch 1st row > >>> of table-1 and remaining rows of table-2 ??? > >> > >> In the join loop, this VDBE program does not fetch any rows from the > >> second table: > >> > >> explain query plan select * from em,l where l.fame=em.age; > >> 0|0|0|SCAN TABLE em > >> 0|1|1|SEARCH TABLE l USING AUTOMATIC COVERING INDEX (fame=?) > >> > >> All accesses to "l" are actually handled by the temporary index (which > >> is created by instructions 5..12). One index search is enough to > >> determine whether a fame value exists. > > > > Then why there is a loop (Next opcode at 23rd instruction) over second > > table when it created an index ? > > Because there might be multiple index entries with the same fame value. > > > Regards, > Clemens > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
