SQL question

[Parker, Ron]

> -----Original Message-----
> From: Brian Densmore [mailto:DensmoreB at ctbsonline.com]

> 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

If you look at the nesting you see that the message table comes from the
first inner join and the bad_marks table from the nested join.  However,
where clauses apply to the overall query.  Depending on the optimizer it is
possible that the where clause is hoisting the *.mark columns into the outer
portion of the query.  At best this will bring along more data than
necessary, just to evaluate a redundant where clause.  At worst it may bring
along the cross-product of all of the rows of the nested tables before
evaluating the redundant condition at all in the outer "loop".  This would
result in a very ugly nightmare for tables of the size you have indicated.

Of course I could be completely wrong.  It's been about 12 years since I did
this stuff.