[sqlite] regarding loops in vdbe code
0 000 NULL > 10Transaction1 0 000 NULL > 11VerifyCookie 1 1 000 NULL > 12TableLock 1 2 0 a 00 NULL > 13Goto 0 3 000 NULL > > explain select * from a join b on a.rowid=b.a_rowid where b.f2 = 1; > addr opcode p1p2p3p4 p5 comment > - - -- - > 0 Trace 0 0 000 NULL > 1 Integer1 1 000 NULL > 2 Goto 0 19000 NULL > 3 OpenRead 1 3 1 2 00 b > 4 OpenRead 0 2 1 1 00 a > 5 Rewind 1 16000 NULL > 6 Column 1 1 200 b.f2 > 7 Ne 1 152 collseq(BINARY) 6c NULL > 8 Column 1 0 300 b.a_rowid > 9 MustBeInt 3 15000 NULL > 10NotExists 0 15300 pk > 11Column 0 0 400 a.f1 > 12Column 1 0 500 b.a_rowid > 13Column 1 1 600 b.f2 > 14ResultRow 4 3 000 NULL > 15Next 1 6 001 NULL > 16Close 1 0 000 NULL > 17Close 0 0 000 NULL > 18Halt 0 0 000 NULL > 19Transaction1 0 000 NULL > 20VerifyCookie 1 2 000 NULL > 21TableLock 1 3 0 b 00 NULL > 22 TableLock 1 2 0 a 00 NULL > 23Goto 0 3 000 NULL > > -Urspr?ngliche Nachricht- > Von: Sairam Gaddam [mailto:gaddamsairam at gmail.com] > Gesendet: Dienstag, 17. M?rz 2015 12:26 > An: General Discussion of SQLite Database > Betreff: [sqlite] regarding loops in vdbe code > > When joining a table in sqlite with some condition using where clause, > sqlite sometimes generate less number of loops(Next opcodes) than the > number of tables. > Can anyone explain how sqlite iterates through all the tables when it has > less number of loops. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > Gunter Hick > Software Engineer > Scientific Games International GmbH > FN 157284 a, HG Wien > Klitschgasse 2-4, A-1130 Vienna, Austria > Tel: +43 1 80100 0 > E-Mail: hick at scigames.at > > This communication (including any attachments) is intended for the use of > the intended recipient(s) only and may contain information that is > confidential, privileged or legally protected. Any unauthorized use or > dissemination of this communication is strictly prohibited. If you have > received this communication in error, please immediately notify the sender > by return e-mail message and delete all copies of the original > communication. Thank you for your cooperation. > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] regarding loops in vdbe code
When joining a table in sqlite with some condition using where clause, sqlite sometimes generate less number of loops(Next opcodes) than the number of tables. Can anyone explain how sqlite iterates through all the tables when it has less number of loops.
[sqlite] regarding loops in vdbe code
You define table em(ployee) to have the field "name" (which is defined as numeric affinity) as the primary key. This forces SQLite to create a unique index on this table. You also define table lo(cation) to have the field "name" (which is defined as numeric affinity) as the primary key. You specify lo.name=em.name in your WHERE clause. SQLite query planner thinks: Q:Given a specific value from an em.name, how many rows would I expect to retrieve? A: Since "name" is a UNIQUE PRIMARY KEY the answer is "not more than one" The same logic holds for idv.id=mny.id So to generate all the rows of the result, SQLITE has to - LOOP over em - SEEK the corresponding lo entry - LOOP over idv - SEEK the corresponding mny entry Which means there are |em| * |idv| entries in the result set. -Urspr?ngliche Nachricht- Von: Sairam Gaddam [mailto:gaddamsairam at gmail.com] Gesendet: Dienstag, 17. M?rz 2015 13:42 An: General Discussion of SQLite Database Betreff: Re: [sqlite] regarding loops in vdbe code But in my example there are multiple rows which satisfy the condition. My example: sql="create table em(name int primary key,age int);"\ "create table idv(id int primary key,name text);"\ "create table mny(id int primary key,sal int);"\ "create table lo(name int primary key,addr text);"; sql="insert into em values(44,21);"\ "insert into em values(11,20);"\ "insert into em values(5,20);"\ "insert into idv values(11,44);"\ "insert into idv values(5,11);"\ "insert into idv values(44,180);"\ "insert into mny values(5,1);"\ "insert into mny values(11,5000);"\ "insert into mny values(44,5000);"\ "insert into lo values(5,'NY');"\ "insert into lo values(44,'che');"\ "insert into lo values(11,'NY');"; sql="select * from em,lo,mny,idv where lo.name=em.name and idv.id=mny.id "; VDBE: 0 Init 0 410 00 1 OpenRead 020 2 00 2 OpenRead 190 2 00 3 OpenRead 4 100 k(2,nil,nil) 00 4 OpenRead 270 2 00 5 OpenRead 350 2 00 6 OpenRead 560 k(2,nil,nil) 00 7 Rewind 0 340 00 8 Column 001 00 9 IsNull 1 330 00 10 Affinity 110 D 00 11 SeekGE 4 331 1 00 12 IdxGT4 331 1 00 13 IdxRowid 420 00 14 Seek 120 00 15 Rewind 2 330 00 16 Column 203 00 17 IsNull 3 320 00 18 Affinity 310 D 00 19 SeekGE 5 323 1 00 20 IdxGT5 323 1 00 21 IdxRowid 540 00 22 Seek 340 00 23 Column 005 00 24 Column 016 00 25 Column 407 00 26 Column 118 00 27 Column 209 00 28 Column 21 10 00 29 Column 50 11 00 30 Column 31 12 00 31 ResultRow580 00 32 Next 2 160 01 33 Next 080 01 34 Close000 00 35 Close100 00 36 Close400 00 37 Close200 00 38 Close300 00 39 Close500 00 40 Halt 000 00 41 Transaction 00 44729 0 01 42 TableLock020 em00 43 TableLock090 lo00 44 TableLock070 mny 00 45 TableLock050 idv 00 46 Goto 010 00 How sqlite works in this case ? because there are 4 tables and for only 2 tables(or indices) it opened loops ? The next opcodes belongs to which tables in this case? On Tue, Mar 17, 2015 at 5:47 PM, Hick Gunter wrote: > If there can be not more than one row that satisfies the constraints (i.e. > the constraints specifiy a unique key) and there is an index (express > or implied or autocreated) available, then a simple index lookup will suffice. > > How many rows do you expect to have a rowid of 1? > How
[sqlite] regarding loops in vdbe code
If there can be not more than one row that satisfies the constraints (i.e. the constraints specifiy a unique key) and there is an index (express or implied or autocreated) available, then a simple index lookup will suffice. How many rows do you expect to have a rowid of 1? How many rows do you expect to read from table a for each row of table b if a_rowid is a foreign key? create temp table a (f1 integer); create temp table b (a_rowid integer, f2 integer); .explain explain select * from a where rowid = 1; addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 000 NULL 1 Integer1 1 000 NULL 2 Goto 0 10000 NULL 3 OpenRead 0 2 1 1 00 a 4 MustBeInt 1 8 000 NULL 5 NotExists 0 8 100 pk 6 Column 0 0 300 a.f1 7 ResultRow 3 1 000 NULL 8 Close 0 0 000 NULL 9 Halt 0 0 000 NULL 10Transaction1 0 000 NULL 11VerifyCookie 1 1 000 NULL 12TableLock 1 2 0 a 00 NULL 13Goto 0 3 000 NULL explain select * from a join b on a.rowid=b.a_rowid where b.f2 = 1; addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 000 NULL 1 Integer1 1 000 NULL 2 Goto 0 19000 NULL 3 OpenRead 1 3 1 2 00 b 4 OpenRead 0 2 1 1 00 a 5 Rewind 1 16000 NULL 6 Column 1 1 200 b.f2 7 Ne 1 152 collseq(BINARY) 6c NULL 8 Column 1 0 300 b.a_rowid 9 MustBeInt 3 15000 NULL 10NotExists 0 15300 pk 11Column 0 0 400 a.f1 12Column 1 0 500 b.a_rowid 13Column 1 1 600 b.f2 14ResultRow 4 3 000 NULL 15Next 1 6 001 NULL 16Close 1 0 000 NULL 17Close 0 0 000 NULL 18Halt 0 0 000 NULL 19Transaction1 0 000 NULL 20VerifyCookie 1 2 000 NULL 21TableLock 1 3 0 b 00 NULL 22TableLock 1 2 0 a 00 NULL 23Goto 0 3 000 NULL -Urspr?ngliche Nachricht- Von: Sairam Gaddam [mailto:gaddamsairam at gmail.com] Gesendet: Dienstag, 17. M?rz 2015 12:26 An: General Discussion of SQLite Database Betreff: [sqlite] regarding loops in vdbe code When joining a table in sqlite with some condition using where clause, sqlite sometimes generate less number of loops(Next opcodes) than the number of tables. Can anyone explain how sqlite iterates through all the tables when it has less number of loops. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] regarding loops in vdbe code
Column 5 0 11 00 r[11]=idv.id 39Column 3 1 12 00 r[12]=idv.name 40ResultRow 5 8 000 output=r[5..12] 41Noop 0 0 000 End WHERE-core 42Noop 0 0 000 End WHERE-loop3: idv 43 Next 2 22001 44 Noop 0 0 000 End WHERE-loop2: mny 45 Noop 0 0 000 End WHERE-loop1: lo 46Next 0 10001 47Noop 0 0 000 End WHERE-loop0: em 48Close 0 0 000 49Close 1 0 000 50Close 4 0 000 51Close 2 0 000 52Close 3 0 000 53Close 5 0 000 54Halt 0 0 000 55Transaction0 0 4 0 01 56TableLock 0 2 0 em 00 iDb=0 root=2 write=0 57TableLock 0 8 0 lo 00 iDb=0 root=8 write=0 58TableLock 0 6 0 mny00 iDb=0 root=6 write=0 59TableLock 0 4 0 idv00 iDb=0 root=4 write=0 60Goto 0 1 000 sqlite> --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-Original Message- >From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- >bounces at mailinglists.sqlite.org] On Behalf Of Sairam Gaddam >Sent: Tuesday, 17 March, 2015 06:42 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] regarding loops in vdbe code > >But in my example there are multiple rows which satisfy the condition. > >My example: > >sql="create table em(name int primary key,age int);"\ >"create table idv(id int primary key,name text);"\ >"create table mny(id int primary key,sal int);"\ >"create table lo(name int primary key,addr text);"; > >sql="insert into em values(44,21);"\ >"insert into em values(11,20);"\ >"insert into em values(5,20);"\ >"insert into idv values(11,44);"\ >"insert into idv values(5,11);"\ >"insert into idv values(44,180);"\ >"insert into mny values(5,1);"\ >"insert into mny values(11,5000);"\ >"insert into mny values(44,5000);"\ >"insert into lo values(5,'NY');"\ >"insert into lo values(44,'che');"\ >"insert into lo values(11,'NY');"; > >sql="select * from em,lo,mny,idv where lo.name=em.name and idv.id=mny.id >"; > >VDBE: > > 0 Init 0 410 00 > 1 OpenRead 020 2 00 > 2 OpenRead 190 2 00 > 3 OpenRead 4 100 k(2,nil,nil) 00 > 4 OpenRead 270 2 00 > 5 OpenRead 350 2 00 > 6 OpenRead 560 k(2,nil,nil) 00 > 7 Rewind 0 340 00 > 8 Column 001 00 > 9 IsNull 1 330 00 > 10 Affinity 110 D 00 > 11 SeekGE 4 331 1 00 > 12 IdxGT4 331 1 00 > 13 IdxRowid 420 00 > 14 Seek 120 00 > 15 Rewind 2 330 00 > 16 Column 203 00 > 17 IsNull 3 320 00 > 18 Affinity 310 D 00 > 19 SeekGE 5 323 1 00 > 20 IdxGT5 323 1 00 > 21 IdxRowid 540 00 > 22 Seek 340 00 > 23 Column 005 00 > 24 Column 016 00 > 25 Column 407 00 > 26 Column 118 00 > 27 Column 209 00 > 28 Column 21 10 00 > 29 Column 50 11 00 > 30 Column 31 12 00 > 31 ResultRow580 00 > 32 Next
