In article 7r2@aladdin.iii.org.tw, idpt426@tpts1.seed.net.tw () writes:
Srinivasa R.Eddula - 1R9
> > Subject: Best way to partially clear a table
> >
> > Anybody have any good idea about the best way to do the following under Sybase
> > System 10.
> >
> > I have one table with > 500,000 rows. All rows have a date and I want to
> > 'retire' old data before a certain date which would cut the table in roughly
> > down to half the number of rows. I simply want to throw these rows out for now.
> >
> > How would I do this without filling up the transaction log? I'd like to do this
> > without transaction logging, but Sybase does support this. I could write a
> > threshold action which would keep dumping the log out and then throw them away
> > and dump the database when I'm done. There are some quirks about 10.0.1 and
> > dumping the transaction log on a threshold, so I'm hesitant to persue this. Any
> > other ideas that don't involve writing my own program using BCP-like functions?
> > BCP itself wouldn't help here much because there is no selectivity.
> >
> >
> > --
> > Steve Rosen Fox Chase Cancer Center
> > SM_R...@fccc.edu 7701 Burholme Avenue
> > (215) 728-3665 Philadelphia, PA 19111
> >try:
> 1) bcp out the original table
>
> 2) select into a temp table where the date is one that you want to keep
>
> 3) drop all indexes from the original table
>
> 4) truncate the original table
>
> 5) insert into the original table, selecting all from the temp table
>
> 6) rebuild the indexs on the original tab
le
>
> Good Luck (I may be totaly wrong, but I don't think that any of these actions
> are writing to the transaction logs.)
>
> David
>
The other way would be to set the row count variable to 1000 (Ex:) After
1000 rows are deleted select stops and you can clear log at this time. Do this
in aloop till all rows are done.
Ex:
set rowcount 1000
select @rows_left = 1
while @rows_left > 0
begin
update PrDayTb set TotMgmtFeeA = 0,
TotUndwrtrFeeA = 0,
SellgExpA = 0,
LossOverSellA = 0
from PrDayTb where AsOfDt < @cur_date and (TotMgmtFeeA != 0 or TotUndwrtrFeeA != 0 or SellgExpA != 0 or LossOverSellA != 0)
select @rows_left = @@rowcount
dump transaction SyndDb with no_log
end
Here rows_left keeps track of the no of rows left and does till all rows are
updated.
Srinivasa
(sed...@mhfl.sbi.com)