Re: Correlated update

From: Gerard H. Pille <ghp_at_skynet.be>
Date: Sun, 05 Dec 2010 10:47:20 +0100
Message-ID: <4cfb5f3b$0$14254$ba620e4c_at_news.skynet.be>

joel garry schreef:
> 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?
>

?????

perhaps?? indices?? He's just created the damned table, and, God forbid, Oracle does not yet create indexes on its own initiative. Received on Sun Dec 05 2010 - 03:47:20 CST