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
There are no comments yet on this issue.