SQL question
Brian Densmore
DensmoreB at ctbsonline.com
Fri Aug 8 22:22:17 CDT 2003
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
>
>
>
More information about the Kclug
mailing list