Mike Mascari wrote:
>
> I would avoid using the INTERSECT/EXCEPT code since the query rewriter
> rewrites these to IN clauses which cannot use indexes. As soon as the tables
> grow beyond more than a couple hundred rows, the statment becomes unusable.
> Instead, I would use a correlated subquery with an EXISTS/NOT EXISTS test
> against the criteria for which you are searching:
>
> SELECT t1.a, t1.b FROM dummy t1
> WHERE EXISTS (SELECT t2.a FROM dummy t2 WHERE t1.a = t2.a)
> ....
>
> then, if you need a comparison of the entire row in the correlated subquery,
> you could use a clause such as
>
> SELECT t1.a, t1.b FROM dummy t1
> WHERE EXISTS (SELECT t2.a FROM dummy t2 WHERE t1.a = t2.a AND
> t1.b IS NULL and t2.b IS NULL);
>
> Hope that helps,
>
> Mike
Yes, thanks.
But there is still a problem. I'm generating queries from user selection and
linking them in a serie.
Using intersect this was very simple (yes, it was slow,but flexible). Using the
form you proposed queries
has to be nested and in every level the table should have an unique name and a
t1.a = t2.a .. tn-1.a = tn.a
has to be generated, which is not a big problem but I have really too much of
this "query building blocks"
and rewrite them to coform the new model will be painful and buggy (many
queries are too long to fit in
a row, and what you don't see well you don't write well :-) ). I hoped that
there was a "plugin" solution
but Murphy was right :-)
Another question: how does the rewriter rewrite the intersect/except query if
it has many fields?
as far as I know the IN clause accepts only subqueries wich return one column
only.
Can I someway (assuming that performance is not a point) force INTERSECT
rewriter to generate code
that confront only the primary key and not all the fields?
Thanks.
Marko Mikulicic
