Ordering dependency problem
Date: 16 Jan 2002 10:44:27 -0800
Message-ID: <cee59be7.0201161044.3736ce40_at_posting.google.com>
Hi, here I encounter another problem on ordering dependencies:
> -----
> case (1)
> Empname salary manager
> Smith 10 Brown
> Jones 9 None
> Brown 11 Jones
>
> case (2)
> Empname salary manager
> Jones 9 None
> Brown 11 Jones
> Smith 10 Brown
> -----
A query want to do a update which gives a 20% pay cut to all employees who earn more than their managers. (This oringinates from M.Stonebraker's old paper In CACM, 1981, "Operating system support for database management" section 5.2. It is in the context of concurrency contorl, no answer is given. You can find it in ACM portal.)
The problem is: the update result will be different due to the different record sequences in the disk pages. For case(1) only Brown's salary is changed, for case(2), Brown and Smith's salary will be changed.
So how to solve this problem in the DBMS?
My answer is:
(1) Maybe this is the problem of SQL evaluation engine. That is, the
inner evaluation should do "Repeat ... unitl no further row is
updated" instead of currently just one time scan. In fact, in most
cases, such as "update emp set name = "xiaowang" where name =
"wang";", will need only 2 times of scan, 1 is for the update action,
the other is for verify there is no more update are needed. But for
this case, the SQL engine has to do O(n) times scan.
(2) Maybe this is the problem due to the table design. That is, the table is not a x-NF well-formed (x may >=3). So it has the problem of updates.
What's your opinion on this problem? Seems old problem, but interesting.
Qingqing Received on Wed Jan 16 2002 - 19:44:27 CET