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
>

Reply via email to