Update of table using WHERE id = <cursor>.<id> ignores index on field ID ------------------------------------------------------------------------
Key: CORE-4675 URL: http://tracker.firebirdsql.org/browse/CORE-4675 Project: Firebird Core Issue Type: Bug Components: Engine Reporter: Pavel Zotov DDL: ==== recreate table ttt(id int primary key using index pk_ttt_id, x int, y int); commit; insert into ttt select row_number()over(), rand()*10, rand()*100 from rdb$types, rdb$types rows 10000; commit; create index ttt_x on ttt(x); commit; Test-1 (fast): ========== set term ^; execute block as declare a_x int; declare v_id int; declare v_x int; declare v_y int; declare c_upd cursor for (select id, x, y from ttt where x = :a_x); begin a_x = 5; open c_upd; while (1=1) do begin fetch c_upd into v_id, v_x, v_y; if (row_count = 0) then leave; update ttt v set y = c_upd.x, x = c_upd.y where v.id = :v_id; ------------------------------ ::: key is specified by VARIABLE which has value from FETCH statement end close c_upd; end ^ set term ;^ rollback; Trace-1: ====== Select Expression -> Filter -> Table "TTT" as "C_UPD TTT" Access By ID -> Bitmap -> Index "TTT_X" Range Scan (full match) Select Expression -> Filter -> Table "TTT" as "V" Access By ID -> Bitmap -> Index "PK_TTT_ID" Unique Scan 0 records fetched 26 ms, 165 read(s), 15 write(s), 18741 fetch(es), 3034 mark(s) Table Natural Index Update Insert ************************************************************************ RDB$INDICES 14 RDB$RELATION_CONSTRAINTS 200 TTT 2016 1008 Test-2 (VERY slow): =============== set term ^; execute block as declare a_x int; declare v_id int; declare v_x int; declare v_y int; declare c_upd cursor for (select id, x, y from ttt where x = :a_x); begin a_x = 5; open c_upd; while (1=1) do begin fetch c_upd; -- into v_id, v_x, v_y; if (row_count = 0) then leave; update ttt v set y = c_upd.x, x = c_upd.y where v.id = c_upd.id; --------------------------------- ::: key is specified by CURSOR field using "cursor name + dot + field" syntax end close c_upd; end ^ set term ;^ rollback; Trace-2: ======= Select Expression -> Filter -> Table "TTT" as "C_UPD TTT" Access By ID -> Bitmap -> Index "TTT_X" Range Scan (full match) Select Expression -> Filter -> Table "TTT" as "V" Full Scan 0 records fetched 8669 ms, 20317223 fetch(es), 3015 mark(s) Table Natural Index Update ************************************************************** TTT 10080000 1008 1008 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira ------------------------------------------------------------------------------ Dive into the World of Parallel Programming. The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net/ Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel