SQL question

[Jim Herrmann]

Dave Hull wrote:

>On Fri, 8 Aug 2003, Brian Densmore wrote:
>
>  
>
>>The query is :
>>
>>use xtra_edi 
>>delete msg_element 
>>from msg_element inner join message 
>>   on msg_element.indice = message.indice
>>   inner join bad_marks on bad_marks.mark = message.mark
>>where message.mark = bad_marks.mark
>>
>>    
>>
>Have you tried something like this:
>
>select
>   'delete from msg_elements A where A.indice = ''' || B.indice || '';'
>from
>   messages B,
>   bad_marks C
>where
>   C.mark = "Bad mark value" and
>   B.mark = C.mark
>
>which will give you a long list of delete statements which you should be able 
>to feed back into the machine. Splitting the job this way may be more (or 
>less) efficient.
>
>Sorry I'm not more help.
>  
>

Actually this is a good idea if you must use SQL, because it would let 
you put commits in at any point you want, assuming you can edit a file 
that large.  You could also do this in the select clause:

  'delete from msg_elements A where A.indice = ''' || B.indice || ''; COMMIT;'

However, performing a commit after every row for 6 million rows would 
take forever.

If REORG is not an option, i.e. the DBMS doesn't have a REORG with a 
DISCARD option, but you can still use utilities, try unloading using a 
query, or exporting depending ont what's available, the rows you want to 
keep.  Then do a load replace of the table or tablespace (depends on 
your physical architecture) using the data you extracted.

HTH,
Jim