Raimund Jacob wrote:
> Zabach, Elke wrote:
>
> hello!
>
> >>i am constructing a rather longish join where i want to
> >>select distinct
> >>rows only. however, i know that the join-rows i collect are
> distinct
> >>within the first two columns (it's a primary key of one of the join
> >>partners). many more columns are selected.
>
> > Always the whole result will be tested for distinctness.
> There is no chance
> > to tell 'only the first x columns plus column y and z
> should be checked' -->
> > therefore no chance to provide this info to the optimizer.
> >
> > But,
> > maybe I did not understand your explanation correctly, but you mean
> > that you do have a result like this (without DISTINCT)
> >
> > c1 c2 c3 c4
> > ---------------------
> > a b 1 2
> > a b 7 3
> > f s 2 4
> >
> > and you want to say DISTINCTNESS for c1 and c2, no matter
> what the rest of
> > the columns show?
> > No, that must be a misunderstanding.
>
> misunderstanding. each row with the same c1/c2 has all other columns
> with the same values, too. those are the same rows. they are
> included in
> the resultset multiple times due to redundant conditions in the WHERE
> clause...
>
> > Second try: if you know that two columns are distinct (the
> primary key) and
> > you do have
> > a 1 : 1 -join, then there is no need to say DISTINCT.
>
> mmmh. i join many tables but each table has a primary key that is the
> same as in the first join partner. however, no referential
> constraints
> are defined.
>
> is there a way to tell how many columns the DISTINCT-logic is using?
> could it be part of the execution plan ?
no, there is no way to tell how many columns the DISTINCT-logic
should use.
ALL columns in the output-list are used --> no need to put it in the
execution plan
> > Just an idea (without really catching the problem): could
> it be helpful to
> > use a FROM-select
> > with DISTINCT for one table before joining it with the
> other (depends on the
> > join 1:1, 1:n, n:m)?
>
> i tried it: i created a DISTINCT from-select that created
> primary keys
> of the result set. i then made a join with that and all the other
> tables. it works as expected but the plan is more expensive.
> however, i
> did not check if it is more expensive than the outer DISTINCT.
From selects are prepared first (no primary key, no index in this
intermediate table)
and then the whole select is done.
Therefore it is not astonishing if the execution plan is more
expensive than without
from-select and the chance for direct access to joining rows via
primary key.
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
