SQL question

[Brian Densmore]

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

[not standard sql]
{ I might have been able to write it like this:
use xtra_edi 
delete from msg_element 
 where msg_element.indice in
  ( select message.indice from message
   inner join bad_marks on bad_marks.mark = message.mark
where message.mark = bad_marks.mark)
}

The message table has the mark field indexed, so it shouldn't 
be a full table scan. The msg_element table has the indice 
field indexed so it shouldn't be a full table scan either. 
There are currently 4 records in the bad_mark table, but there 
will be about 100. I know that the where clause is redundant 
and probably not necessary, but I included it just in case 
(I figured it wouldn't slow or speed up the query, but am 
paranoid about deleting the entire msg_element table 
[it took/takes weeks to extract from the legacy system]).

Thanks,
Brian

>...
> Anyway, I, have two questions:
> 1) why should it take 18 hours to run (the delete query should
> have deleted about 1/3 of the data in the database),
> 2) is there anyway to have a single delete query statement
> do periodic commits (as really dangerous as it is)?
> 
> basically what I have is this:
> 
> table bad_marks (uid,mark)
> table messages  (indice,mark,...)
> table msg_elements (id,indice,...)
> 
> where bad_marks.mark 1->M/1->0 messages.mark, uid = PK
>       messages.indice 1->M msg_element.indice, message.indice = PK,
> message.mark = indexed
>       msg_element id = PK, indice = indexed
> and there is no referential integrity turned on (yet).
> 
> Thanks,
> Brian
> 
> "Three OS's from corporate-kings in their towers of glass,
> Seven from valley-lords where orchards used to grow,
> Nine from dotcoms doomed to die,
> one from the dark lord Gates on his dark throne
> In the Land of Redmond where the Shadows lie.
> one OS to rule them all, one OS to find them,
> one OS to bring them all and in the darkness bind them,
> In the Land of Redmond where the Shadows lie."    john thrum
> 
> 
> 
> majordomo at kclug.org
> 
> 
>