Re: tuning SQL

On Tue, 29 Jan 2002 17:45:34 +0000 (UTC), azhang(at)verisign(dot)com ("Zhang,
Anna") wrote:

>Thanks Peter Darley, Ross J. Reedstrom and Tom lane!!
>How silly am I! Your messages reminds me. Actually I want to insert rows of
>contact_discard table which are not exists in contact table to contact table
>(some duplicates in two tables), first I run
>
>insert into contact
>select * from contact_discard a
>where not exists ( select 1 from contact b where b.contacthandle =
>a.contacthandle);
>
>It seems takes forever, I killed it after hours(note: contact table has 4
>indexes). Then I tried to figure out how many rows that are not duplicated.
>Now my problem turns to insert performance, in oracle it takes only a few
>minues.
>
>Thanks!
>
>Anna Zhang
>

With millions of rows, you also might want to create a temporary
(real) table with just the select statement, then drop ALL the indexes
on contact, do the update, then recreate the indexes.

Bob Hairgrove
rhairgroveNoSpam(at)Pleasebigfoot(dot)com