On Wed, 17 Sep 2003 17:35:22 -0400, "Ryan" <rgaffuri_at_cox.net> wrote:
>
><Kenneth Koenraadt> wrote in message
>news:3f68c498.3658049_at_news.inet.tele.dk...
>> On 17 Sep 2003 12:45:59 -0700, rgaffuri_at_cox.net (Ryan Gaffuri) wrote:
>>
>> >Stage Table has 17 million records with Delete_column = 'Y';
>> >
>> >MAster has 27 million records.
>> >
>> >so I need to
>> >delete from master
>> >where master.pk = stage.pk
>> >and stage.pk = 'Y';
>> >
>> >i tried writing a 'create table as' with a 'not exists' and was
>> >running for 8 hours. killed it. didnt even go to 'killed' status which
>> >means the DML hadnt even started it.
>> >
>> >I tried doing the following: but cant get the outer join right. Is
>> >that faster than a minus?
>> >
>> >any ideas?
>>
>> Hi,
>>
>> I suppose you mean "stage.Delete_column = 'Y' instead of stage.pk ='Y'
>> above.
>>
>> Try
>>
>> delete from master a
>> where exists (select 1
>> from stage b
>> where a.pk = b.pk
>> and b.Delete_column = 'Y');
>>
>> Depending on the actual number or rows deleted and the size (row
>> width) of master table, the transcation may become very large, be sure
>> to have enough rollback space.
>>
>> Indexes on STAGE.PK and MASTER.PK would help a lot.
>>
>> Depending on the different column value distribution in STAGE you
>> might also consider a bitmax index on STAGE.DELETE_COLUMN.
>>
>> - Kenneth Koenraadt
>>
>>
>>
>
>doesnt exist always force a full table scan on the sub-query?
>
>
No.
Received on Thu Sep 18 2003 - 14:24:42 CDT
