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 ?
> 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.
thanks for any advice,
Raimund
--
Raimund Jacob
\|/ ____ \|/
Pinuts Media+Science GmbH "@'/ ,. \`@"
Potsdamer Str. 96 /_| \__/ |_\
D-10785 Berlin \__U_/
http://www.pinuts.de/ voice: +49 30 590090313 fax: +49 30 590090390
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
