Issue Details (XML | Word | Printable)

Key: CORE-4447
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Pavel Zotov
Votes: 0
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Positioned UPDATE statement prohibits index usage for the subsequent cursor field references

Created: 02/Jun/14 02:44 PM   Updated: 23/Sep/15 11:25 AM
Component/s: None
Affects Version/s: 3.0 Alpha 2
Fix Version/s: 3.0 Beta 1

QA Status: Done successfully


 Description  « Hide
LI-T6.3.0.31152

DDL:
====
recreate table ts(id int, x int, y int, z int, constraint ts_pk_id primary key (id) );
recreate table tt(x int, y int, z int, constraint tt_pk_xy primary key (x,y) );
commit;

insert into ts
select row_number()over(), rand()*10, rand()*10, rand()*10
from rdb$types;
commit;

insert into tt select distinct x,y,0 from ts;
commit;

Tests.

var-1.
=====
execute block as
begin
  for select id,x,y,z from ts as cursor c
  do begin
     update ts set id = id where id = c.id; -- <<<<<<<<<<<<<< ::: NB ::: we lock record in source using it's PK
     update tt t set t.z = t.z + c.z where t.x=c.x and t.y = c.y;
  end
end

Trace:
=====
PLAN (TS INDEX (TS_PK_ID))
PLAN (T INDEX (TT_PK_XY))
PLAN (C TS NATURAL)
0 records fetched
      5 ms, 6341 fetch(es), 1010 mark(s)

Table Natural Index Update
*************************************************************
TS 252 252 252
TT 252 252

-- OK, only INDEXED reads for table TT in this sample.

var-2.
=====
execute block as
begin
  for select id,x,y,z from ts as cursor c
  do begin
     update ts set id = id where current of c; -- <<<<<<<<<<<<<<< ::: NB ::: we lock record in source using "current of" clause
     update tt t set t.z = t.z + c.z where t.x=c.x and t.y = c.y;
  end
end

Trace:
PLAN (T NATURAL)
PLAN (C TS NATURAL)
0 records fetched
     25 ms, 56489 fetch(es), 1010 mark(s)

Table Natural Index Update
**************************************************************
TS 252 252
TT 25704 252

Here we get over 25'000 NATURAL reads of table TT and almost 56500 fetches.
The only difference between var-1 and var-2 is the way which is used to lock records in the SOURCE table (TS) rather than target one.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov made changes - 02/Jun/14 03:20 PM
Field Original Value New Value
Assignee Dmitry Yemanov [ dimitr ]
Dmitry Yemanov made changes - 04/Jun/14 09:48 AM
Summary Indices are ignored on table which receives data processed inside cursor when using "AS CURSOR <cursor_name>" clause and lock source record using "WHERE CURRENT OF <cursor_name>" Positioned update/delete prohibits index usage for the subsequent cursor field references
Dmitry Yemanov made changes - 04/Jun/14 09:50 AM
Summary Positioned update/delete prohibits index usage for the subsequent cursor field references Positioned UPDATE statement prohibits index usage for the subsequent cursor field references
Dmitry Yemanov made changes - 04/Jun/14 09:51 AM
Status Open [ 1 ] Resolved [ 5 ]
Fix Version/s 3.0 Beta 1 [ 10332 ]
Resolution Fixed [ 1 ]
Pavel Zotov made changes - 29/May/15 09:36 PM
Status Resolved [ 5 ] Resolved [ 5 ]
QA Status Done successfully
Pavel Cisar made changes - 23/Sep/15 11:25 AM
Status Resolved [ 5 ] Closed [ 6 ]