Then why there is a loop (Next opcode at 23rd instruction) over second
table when it created an index ?

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.
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

Reply via email to