Re: Help w/large deletes on fragmented table...

In article <mickmED0n2n.Ltq@netcom.com>, Mickey Mestel <mickm@netcom.com> writes >hi, > > i posted on this a while ago, and got some good response, but am back >again to a similar problem. i have a tmp table built with two columns that >identify which rows in certain tables i need to delete. this tmp table >typically has about 400,000 rows, and on the larger tables, that is a one to >one match, i.e. there will be 400,000 deltes from these tables. > > in response to an answer to my last post, i'm using rowids for all >the other tables involved in the purge process, but that is obviously out >the window for my fragmented table. > > i'm trying a number of strategies, similar to the following: > > delete from table > where exists ( select * from tmp_table where > table.col1 = tmp_table.col1 > and table.col2 = tmp_table.col2)> > this is *very* slow. > > delete from table > where col1 || col2 = (select col1 || col2 from tmp_table)> > i'm testing this now, but earlier indications were that this would >also be slow. > > another method we are trying is to use a cursor on the tmp table and >then walk through the deletes, one by one, but this is painfully slow. > > declare cursor tmp_curs for > select * from tmp_table > while(..) > fetch tmp_curs into val1, val2> > delete from table where col1 = val1 and col2 = val2 Make sure you prepare this delete BEFORE the foreach loop.... > end > > there has to be a way to speed up these deletes, i just haven't found >it yet. > > can anyone lend a hand??? > > thanks. > > mickm > > > > >-- > >_____________________________________________________________________________ >Mickey Mestel mickm@netcom.com > >-on a beach in thailand to a beautiful, stoned, norwegian woman: > "..yeah, it's just another foreign country without ice." >----------------------------------------------------------------------------- -- David Williams