>> The listing 2 example:
>> 1 SELECT D_TAX, D_NEXT_O_ID
>> 2 INTO :dist_tax , :next_o_id
>> 3 FROM OLD TABLE ( UPDATE DISTRICT
>> 4 SET D_NEXT_O_ID = D_NEXT_O_ID + 1
>> 5 WHERE D_W_ID = :w_id
>> 6 AND D_ID = :d_id
>> 7 ) AS OT
> A lot of this is non-standard SQL, so I can't really tell what DB2 is
> here. Can you explain it?
Quote from the article at:
> First, DB2 deals with the DISTRICT table. Data needs to be returned
> and an update needs to be performed. Conventional wisdom states that
> this requires 2 SQL statements, and that the UPDATE ought to be done
> prior to the SELECT; otherwise deadlocks may occur as concurrency
> DB2 however supports a new SQL feature which is in the process of
> being standardized. This feature allows access to what is known as
> transition tables in triggers. The OLD TABLE transition table holds
> the original state of the affected rows before they are processed by
> the UPDATE or DELETE statement. The NEW TABLE transition table holds
> the affected rows immediately after an INSERT or UPDATE was processed.
> That is the state prior to when AFTER triggers fire. Users with a
> Microsoft or Sybase background may know these tables by the names
> DELETED and INSERTED.
So, if I understand they use only ONE query to get the UPDATE and the
SELECT of the old value.
In response to
pgsql-performance by date
|Next:||From: Mike Rylander||Date: 2004-12-04 01:53:07|
|Subject: Re: Alternatives to Dell?|
|Previous:||From: Kiran Mukhyala||Date: 2004-12-03 22:31:11|
|Subject: Performance difference in similar queries|