Re: Large Table Reorg

In article <5bi9o7$fcs@cssun.mathcs.emory.edu>, wardr@gusco.com (Rick Ward) wrote: >Using: ONLINE DSA V7.13 > on SUN SOLARIS 2.5 > >We are building a large(ish) DSS system, and some table will become 10GB in size. Does anyone >out there have tables this big which the rebuild to reorganise the data ? If so, how do you >do this given that unloading the table to disk is not viable because of (the Sun Solaris) 2GB >UNIX file size limit ? I do not want to unload this to tape because 1, its slow and 2, tapes are >not the most reliable media. > >Thanks, We've done this several ways: 1- create a new table, insert into <new_table> select * from <old_table>, drop old_table, rename new table. Your 10 GB may include space taken up by indexes which means you may not have to have a full 10 GB available to create a new table. You can drop the old_table then create indexes on the new_table. 2- unload to a named pipe that you have UNIX compress reading from and sending it to a file, drop & create table then reload. I have seen compress get 15:1 even 25:1 compression ratios on unloaded tables. It all depends on the data. $mkfifo /tmp/unload.pipe $dd if=/tmp/unload.pipe |compress -c >/tmp/table.unl.Z & #syntax may be #questionable $dbaccess <DB> -<<EOF >unload to /tmp/unload.pipe >select * from <TB>>EOF If the compressed unload still will not fit in a 2 GB filesystem you may need to logically break it up or redirect your compress to a tape device. Matt Bentley mbentley@xmission.com