Re: sql question

In article <66hkm2$42m_at_cssun.mathcs.emory.edu>,   johnl_at_informix.com (Jonathan Leffler) wrote:
> ...
> That's a complicated way of writing:
>
> BEGIN WORK;
> SELECT DISTINCT * FROM Table INTO TEMP Temp1;
> DELETE FROM Table WHERE 1 = 1;
> INSERT INTO Table SELECT * FROM Temp1;
> COMMIT WORK;
>
> This is a reasonable solution for small to medium tables when you have
> enough disk space available for the whole temporary table.
> ...

Never done this kind of stuff but it might be faster to do something along the lines of:

BEGIN WORK;
CREATE TABLE NewTable (...);
LOCK TABLE NewTable IN EXCLUSIVE MODE;
INSERT INTO NewTable SELECT DISTINCT * FROM OldTable; DROP TABLE OldTable;
RENAME TABLE NewTable TO OldTable;
CREATE INDEX ...;
COMMIT WORK; Not as generic but may be better in some circumstances.

John H. Frantz           Power-4gl: Extending Informix-4gl
frantz_at_centrum.is        http://www.rl.is/~john/pow4gl.html

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet