Re: Correlated update

From: joel garry <joel-garry_at_home.com>
Date: Sat, 4 Dec 2010 22:19:28 -0800 (PST)
Message-ID: <fecd6d4e-54be-45b5-af0c-449156b1bd86_at_m20g2000prc.googlegroups.com>

On Dec 3, 11:11 pm, "bob123" <nom..._at_nowhere.com> wrote:
> Hi,
>
> I have the following problem
> The update is very long (more than 4 hours)
> table T_SUBJECTVISITFORMITEMS is 3M rows
>
> How can I do that more quickly ?
>
> Thanks in advance
>
> CREATE TABLE T_ITEMFIRSTDATA_STATUS_ICLD
> AS
> SELECT
>  MIN (ITEMFIRSTDATA) ITEMFIRSTDATA ,
>  SUBJECTID,
>  VISITID,
>  DB_SOURCE
> FROM T_SUBJECTVISITFORMITEMS
> WHERE FORMMNEMONIC = 'STATU'
> GROUP BY SUBJECTID,VISITID,FORMID,DB_SOURCE;
>
> ALTER TABLE  T_SUBJECTVISITFORMITEMS ADD ITEMFIRSTDATA_STATU DATE;
>
> UPDATE  T_SUBJECTVISITFORMITEMS B SET ITEMFIRSTDATA_STATU =
> (SELECT DISTINCT ITEMFIRSTDATA
> FROM T_ITEMFIRSTDATA_STATUS_ICLD  A
> WHERE A.SUBJECTID=B.SUBJECTID
> AND  A.VISITID=B.VISITID
> AND  A.DB_SOURCE= B.DB_SOURCE);
Try posting the plan. The distinct perhaps means a full table scan of ...icld for each row of ...formitems. Would you happen to have any indices? Does your cpu go nuts? Does statspack have something to say about what is going on in that time period?

jg

--
_at_home.com is bogus.
http://www.signonsandiego.com/news/2010/nov/30/jmi-equity-raises-875-million-for-technology-fund/

Received on Sun Dec 05 2010 - 00:19:28 CST