Re: Update TAble problem

On 14 Mar 2006 06:39:19 -0800, "devi" <devivelu_at_gmail.com> wrote:

>
>hi all,
>
>I am creating a sample bug tracker (using Oracle 10g db) and i created
>a hisotry table to
>log the bug history.
>
>The history table contains details like ProblemRecordNo (PRN),
>RecordStatus, Assignee, Reporter.
>The PK for this table is ID.
>The history table contains multiple entries for a Record.
>
>I have inserted a lot of records int History table and now I introduced
>two new fields into History table. They are PreviousStatus and
>PreviousAssignee. I wanted to update the previous values of the Status
>and Assignee for each record.
>Say my History table contains values like
>
>ID PRN Status Assignee
>1 10 Report UserA
>2 10 InProcess UserA
>3 10 Esclated UserB
>
>Now after introducing the Previous fields, the History table should
>look like
>
>ID PRN Status Assignee PreviousStatus PreviousAssignee
>1 10 Report UserA
>2 10 InProcess UserA Report UserA
>3 10 Esclated UserB InProcess UserA
>
>
>In the first record the PreviousStatus and PreviousAssignee are empty
>bse there is no previous values for those two items. the next two
>records contain the previous values of the status and assignee.
>
>I used the following sql statement to update the table, but I get a
>"cannot modify a column which maps to a non key-preserved table" error.
>
>UPDATE (
>SELECT H.PRN, H.ID, H.PREVIOUSSTATUS AS OLDSTATUS, N.STATUS AS
>NEWSTATUS,
>H.PREVIOUSASSIGNEE AS OLDASSIGNEE, N.ASSIGNEE AS NEWASSIGNEE FROM
>HISTORY H,
>(SELECT N.ID, N.PRN, N.STATUS, N.ASSIGNEE FROM HISTORY N ) N
>WHERE H.PRN = N.PRN AND N.ID < H.ID AND N.ID IN
>(SELECT MAX(M.ID) FROM HISTORY M WHERE M.ID < H.ID AND H.PRN = M.PRN) )
>SET OLDSTATUS = NEWSTATUS,
>OLDASSIGNEE = NEWASSIGNEE
>
>So I used a different sql to update the table like
>
> UPDATE History H SET (H.PreviousStatus, H.PreviousAssignee) =
> ( SELECT N.Status, N.Assignee FROM History N
> WHERE H.PRN = N.PRN AND N.ID < H.ID AND N.ID IN
> (SELECT MAX(M.ID) FROM History M WHERE M.ID < H.ID
> AND H.PRN = M.PRN) )
>
>But this sql query takes a long time to update records (say 18000
>records)
>
>Is there anyother way i could update the table with a optimized query
>that doesnt take time to update large set of rows.
>
>Any help?
>
>:(

Apart from the design, which is flawed (not including a timestamp column is a *bad* idea, as who can tell the IDs are going to be consecutive), the second statement contains several redundant predicates probably resulting in an incorrect execution path. It should have been

 UPDATE History H SET (H.PreviousStatus, H.PreviousAssignee) =    ( SELECT N.Status, N.Assignee FROM History N      WHERE H.PRN = N.PRN AND N.ID =
    (SELECT MAX(M.ID) FROM History M WHERE M.ID < N.ID     AND N.PRN = M.PRN) ) PRN obviously needs to be indexed.
Usually this type of query works without problem.  

--
Sybrand Bakker, Senior Oracle DBA