Re: sql question for purge routine
In article <mickmEAysw9.J2w@netcom.com>, Mickey Mestel
<mickm@netcom.com> writes
>hi,
>
> we are doing a purge here on some huge tables, and this is the way
>things are set up. we create a series of temp tables, and the final one has
>two columns which are the values we are matching in the table we want to
>delete from. this is the query that we are using:
>
Simailar to what we do when we archibe old data...
> select a.* from tab_to_purge a, tmp_table b
> where a.col1 = b.col1
> and a.col2 = b.col2
>
> there are indexes on the temp table with update stats run after the
>table and index are created. the index is a composite index on col1, col2.
>this works great for some tables, but not for others, as they don't have the
>proper indexes to make use of this, and we have space and time problems.
>
> this doesn't seem like the best way to do this query, but i can't
>see anything else. the other problem is, what about the delete? how do i
>structure the delete in this case, as i can't say:
>
> delete from tab_to_purge a, tmp_table b
> where a.col1 = b.col1
> and a.col2 = b.col2>
> so how do i handle the deletes?
>
> delete from tab_to purge
> where col1 in (select col1 from tmp_table)
> and col2 in (select col2 form tmp_tabe)>
>doesn't seem like it will cut it. there are 330,000 rows in the temp table.
>
delete from tab_to_purge where 1 in
( select 1 from tab_to_purge a, tmp_table b
where a.col1 = b.col1
and a.col2 = b.col2)
Basically the first select changed to select 1 from.. and in a
subquery.
DO NOT USE THIS IF YOU ARE USING ONLINE 7.X AND FRAGMENTATION BUT...
Personally I prefer to do...
select rowid from tab_to_purge where.... insert into temp t1;
create index t1_ind on t1(rowid);
delete from tab_to_purge where rowid in (select rowid from t1)
as access via rowid is very fast.
> any help, suggestions, hints??
>
> 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