Re: [SQL] Separating data sets in a table
"Andreas Tille" <[EMAIL PROTECTED]> a écrit dans le message de news: [EMAIL PROTECTED] ... > I tried to do the following approach: > >CREATE LOCAL TEMPORARY TABLE ImportOK () INHERITS (Import) ; > >INSERT INTO ImportOK SELECT * FROM Import i > INNER JOIN Ref r ON i.Id = r.Id; > >DELETE FROM Import WHERE Id IN (SELECT Id FROM ImportOK) ; ... > Unfortunately the latest statement is so terribly slow that I can't > imagine that there is a better way to do this. > You must use EXISTS if you work with big tables. EXISTS use indexes, and IN use temporary tables. -- this change nothing for IN : CREATE INDEX import_id_index ON import(id); CREATE INDEX import_ok_id_index ON import_ok(id); -- slow : -- DELETE FROM import WHERE id IN (SELECT id FROM import_ok) ; DELETE FROM import WHERE EXISTS ( SELECT id FROM import_ok AS ok WHERE ok.id = import.id ); ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SELECT ... WHERE ... NOT IN (SELECT ...);
I use a 7.3devel recently build (on a FreeBSD 4.3 box) from CVS sources. I follow all your steps, and psql results : test_db=> \! cat problem.sql --create new temp tbl1 SELECT losteventid AS eventid INTO tbl1 FROM outages; --create new temp tbl2 SELECT regainedeventid AS eventid INTO tbl2 FROM outages; SELECT eventid FROM tbl1 WHERE eventid NOT IN (SELECT eventid FROM tbl2); test_db=> \i problem.sql SELECT SELECT eventid - 119064 119064 60116 16082 16082 16303 16082 92628 92628 60083 (10 rows) "Yon Den Baguse Ngarso" <[EMAIL PROTECTED]> a écrit dans le message de news: [EMAIL PROTECTED] > Oops correction. > ... cut > > myhost=# SELECT eventid FROM tbl1 WHERE eventid NOT IN (SELECT eventid FROM tbl2); > eventid > - > (0 rows) > > TIA, > Yon > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
