-----Urspr?ngliche Nachricht----- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von nomad at null.net Gesendet: Freitag, 19. Februar 2016 11:01 An: SQLite mailing list Betreff: [sqlite] EXPLAIN QUERY PLAN for non-SELECT statements?
I wanted to see how SELECT queries within triggers are using indexes (or not) which of course lead me to https://www.sqlite.org/eqp.html: EXPLAIN QUERY PLAN is most useful on a SELECT statement, but may also be appear with other statements that read data from database tables (e.g. UPDATE, DELETE, INSERT INTO ... SELECT). However I don't get any output from EQP on non-SELECT queries: .version -- SQLite 3.9.1 2015-10-16 17:31:12 767c1727fec4ce11b83f25b3f1bfcfe68a2c8b02 create table t( a integer not null, b integer not null ); create index t_a on t(a,b); create trigger t_ai after insert on t for each row begin insert into t(a,b) select a,new.a from t where a < new.a and a = b; end; insert into t(a,b) values(1,1); insert into t(a,b) values(2,2); insert into t(a,b) values(3,3); select * from t order by a,b; -- a b -- ---------- ---------- -- 1 1 -- 1 2 -- 1 3 -- 2 2 -- 2 3 -- 3 3 explain query plan insert into t(a,b) values(4,4); -- No output! Is the above lack of output expected? Is there some way other than cutting and pasting and substituing NEW.*/OLD.* values to see what my triggers are doing? Mark -- Mark Lawrence The INSERT itself has no query plan. The trigger processing is stored in a separate SQLite object program that is not accessible tot he explain statement. asql> explain create trigger t_ai after insert on t for each row begin insert into t (a,b) select a,new.a from t where a < new.a and a = b; end; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 NULL 1 Goto 0 18 0 00 NULL 2 Integer 0 1 0 00 NULL 3 OpenWrite 0 1 1 5 00 sqlite_temp_master 4 NewRowid 0 2 0 00 NULL 5 String8 0 3 0 trigger 00 NULL 6 String8 0 4 0 t_ai 00 NULL 7 String8 0 5 0 t 00 NULL 8 Integer 0 6 0 00 NULL 9 String8 0 7 0 CREATE TRIGGER t_ai after insert on t for each row begin insert into t (a,b) select a,new.a from t where a < new.a and a = b; end 00 NULL 10 MakeRecord 3 5 8 aaada 00 NULL 11 Insert 0 8 2 18 NULL 12 AddImm 1 1 0 00 NULL 13 Close 0 0 0 00 NULL 14 Integer 5 9 0 00 NULL 15 SetCookie 1 1 9 00 NULL 16 ParseSchema 1 0 0 type='trigger' AND name='t_ai' 00 NULL 17 Halt 0 0 0 00 NULL 18 Transaction 1 1 0 00 NULL 19 VerifyCookie 1 4 0 00 NULL 20 TableLock 1 1 1 sqlite_temp_master 00 NULL 21 Goto 0 2 0 00 NULL asql> explain insert into t values (4,4); addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 NULL 1 Goto 0 23 0 00 NULL 2 Integer 0 1 0 00 NULL 3 OpenWrite 0 2 1 2 00 t 4 OpenWrite 1 3 1 Keyinfo(2,BINARY,BINARY) 00 tab 5 NewRowid 0 3 0 00 NULL 6 Integer 4 4 0 00 NULL 7 Integer 4 5 0 00 NULL 8 HaltIfNull 19 2 4 t.a may not be NULL 00 NULL 9 HaltIfNull 19 2 5 t.b may not be NULL 00 NULL 10 SCopy 4 6 0 00 NULL 11 SCopy 5 7 0 00 NULL 12 SCopy 3 8 0 00 NULL 13 MakeRecord 6 3 2 ddd 00 NULL 14 IdxInsert 1 2 0 10 NULL 15 MakeRecord 4 2 9 dd 00 NULL 16 Insert 0 9 3 t 1b NULL 17 AddImm 1 1 0 00 NULL 18 Program 0 19 10 program 01 Call: t_ai.default 19 Close 0 0 0 00 NULL 20 Close 1 0 0 00 NULL 21 ResultRow 1 1 0 00 NULL 22 Halt 0 0 0 00 NULL 23 Transaction 1 1 0 00 NULL 24 VerifyCookie 1 3 0 00 NULL 25 TableLock 1 2 1 t 00 NULL 26 Goto 0 2 0 00 NULL 0 Trace 0 0 0 -- TRIGGER t_ai 00 Start: t_ai.default (AFTER INSERT ON t) 1 Integer 0 1 0 00 SELECT eof flag 2 Integer 3 2 0 00 NULL 3 Goto 0 24 0 00 Jump over SELECT coroutine 4 Param 4 3 0 00 new.a -> $3 5 OpenRead 1 3 1 Keyinfo(2,BINARY,BINARY) 00 tab 6 Rewind 1 20 4 0 00 NULL 7 Copy 3 4 0 00 NULL 8 IsNull 4 20 0 00 NULL 9 Affinity 4 1 0 d 00 NULL 10 IdxGE 1 20 4 1 00 NULL 11 Column 1 0 5 00 NULL 12 IsNull 5 19 0 00 NULL 13 Column 1 0 5 00 t.a 14 Column 1 1 6 00 t.b 15 Ne 6 19 5 collseq(BINARY) 6b NULL 16 Column 1 0 7 00 t.a 17 Param 4 8 0 00 new.a -> $8 18 Yield 2 0 0 00 NULL 19 Next 1 10 0 00 NULL 20 Close 1 0 0 00 NULL 21 Integer 1 1 0 00 NULL 22 Yield 2 0 0 00 NULL 23 Halt 2 2 0 00 End of SELECT coroutine 24 OpenEphemeral 2 2 0 00 NULL 25 Yield 2 0 0 00 NULL 26 If 1 31 0 00 NULL 27 MakeRecord 7 2 6 00 NULL 28 NewRowid 2 5 0 00 NULL 29 Insert 2 6 5 00 NULL 30 Goto 0 25 0 00 NULL 31 Integer 0 9 0 00 NULL 32 OpenWrite 3 2 1 2 00 t 33 OpenWrite 4 3 1 Keyinfo(2,BINARY,BINARY) 00 tab 34 Rewind 2 50 0 00 NULL 35 NewRowid 3 11 0 00 NULL 36 Column 2 0 12 00 NULL 37 Column 2 1 13 00 NULL 38 HaltIfNull 19 2 12 t.a may not be NULL 00 NULL 39 HaltIfNull 19 2 13 t.b may not be NULL 00 NULL 40 SCopy 12 14 0 00 NULL 41 SCopy 13 15 0 00 NULL 42 SCopy 11 16 0 00 NULL 43 MakeRecord 14 3 10 ddd 00 NULL 44 IdxInsert 4 10 0 10 NULL 45 MakeRecord 12 2 5 dd 00 NULL 46 Insert 3 5 11 t 1b NULL 47 AddImm 9 1 0 00 NULL 48 Program 8 49 17 program 01 Call: t_ai.default 49 Next 2 35 0 00 NULL 50 Close 2 0 0 00 NULL 51 Close 3 0 0 00 NULL 52 Close 4 0 0 00 NULL 53 ResetCount 0 0 0 00 NULL 54 Halt 0 0 0 00 End: t_ai.default ___________________________________________ 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.
