Re: tuning SQL

On Tue, Jan 29, 2002 at 12:23:17PM -0500, 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.

So, how long does the just the select take? You might be running into
something odd with selecting from and inserting into the same table: I'm
not sure of the visibility rules here. You might be better off selecting
into a new temp table (see: SELECT INTO) then inserting from that.

Ross