Re: sql question

Anthony Mandic wrote: > Michael Cressey wrote: > > Depending on how big this table is you might be better off > > copying the data into a file and using an operating system > > command to remove the duplicate rows. (sort -u on unix). > > Once this has been done you can copy the data back in to the > > database. This is invariably quicker than letting the database > > engine do the work. > > Make sure you've got a good backup first though! > Actually, resorting to the OS should be the last thing > you ought to try. How large a table it is isn't that much > an issue as how much space you have left in the database > that the table resides in. And this would only be the case > if you were to try a 'select distinct ...' of the table into > another table (i.e. make a copy with the duplicate rows > removed). You could remove the duplicates by entering SQL > to find and delete them or creating an index that would > remove them as part of its creation process. This, however, > depends on the specifics of the database server implementation. > Using Unix (or the OS platform, since it may not be Unix), > just adds further overhead - more commands to enter and > more potential points of failure. You'd also need the space > if the table was large. The sort command would be slower > since it would have to work on plain text and the options > to define which columns to sort on becomes tortuous the > more columns you want to cover. Overall, the entire process > would also be slower since you now have to add the time > to copy the data out and load it back in afterwards. Well, I can't generally agree with this last post. I'm not sure where the original question originated, as this has been x-posted a tad, but speaking from the point of Ingres on UNIX, specifically SunOS 5.x, the O/S tools can be much more efficient and faster at certain things than Ingres. It may be that what you want to do is easier to do in Ingres, but Ingres is a generalised tool, good at most things, with some trade-offs involved. A relational database system is not the be-all and end-all - sometimes a "flat" database or even an editor is more appropriate for a job. Really, it comes down to horses for courses. Richard. ~~~~~~~~ -- The Open University is not responsible for content herein, which may be incorrect and is used at readers own risk.