Dirk Vleugels wrote:
> we're using 7.4.3.10 (Build 010-120-035-462). The following statement
> works:
>
> SELECT DISTINCT pkm_document.document_pk, pkm_document.pk,
> pkm_document.version, pkm_document.uploader, pkm_document.uploaddate,
> pkm_document.invalid, pkm_document.primary_group_pk,
> pkm_document.filesize,
> UPPER(pkm_document.filename), UPPER(pkm_document.comment_text),
> att_value.definition_pk, att_value.value,
> UPPER(pkm_user.realname) FROM
> pkm_document, pkm_user, att_value WHERE (pkm_document.parent_dir_id =
> 2630
> AND pkm_document.version != 0 AND pkm_document.pk IN (SELECT
> pkm_document_rights.pkm_document_pk FROM pkm_document_rights WHERE
> pkm_document_rights.group_pk IN (40,660,980)) AND pkm_document.pk =
> att_value.pkm_document_pk (+) AND pkm_document.uploader =
> pkm_user.pk)
> ORDER BY 5 DESC
>
> Execution time roughly 10ms. Sizes of tables:
>
> pkm_document 21k entries, pkm_user 1k entries, att_value 31k entries.
>
> The statement is generated by a persistence framework (via jdbc)
> using a 1.3 jvm. Switching to a 1.4.2 jvm the following statement is
> executed (the order of the tables in the FROM part is different):
>
> SELECT DISTINCT pkm_document.document_pk, pkm_document.pk,
> pkm_document.version, pkm_document.uploader, pkm_document.uploaddate,
> pkm_document.invalid, pkm_document.primary_group_pk,
> pkm_document.filesize,
> UPPER(pkm_document.filename), UPPER(pkm_document.comment_text),
> att_value.definition_pk, att_value.value,
> UPPER(pkm_user.realname) FROM
> att_value, pkm_user, pkm_document WHERE (pkm_document.parent_dir_id =
> 2630
> AND pkm_document.version != 0 AND pkm_document.pk IN (SELECT
> pkm_document_rights.pkm_document_pk FROM pkm_document_rights WHERE
> pkm_document_rights.group_pk IN (40,660,980)) AND pkm_document.pk =
> att_value.pkm_document_pk (+) AND pkm_document.uploader =
> pkm_user.pk)
> ORDER BY 5 DESC
>
> The execution plan (checked via EXPLAIN) is the same, but the second
> statement grabs alls TEMP space and never terminates. This happens
> regardles if we use JDBC oder 'Studio', so it seems the optimizer is
> broken.
Within this version of SAP DB the execution sequence for outer joins
is given by the table sequence in the from clause.
So I think the execution plan is not exactly the same because the
order in the explain plan should vary and this will be your problem.
With newer versions of SAP DB you could change this default behaviour
by executing 'diagnose optimize join outer off' from SQL Studio or dbmcli.
With this enabled join optimization the optimizer tries to find the
best execution plan also for outer join selects.
So please upgrade to a newer version and try your statement with this
enabled join feature.
If the problem still exists afterwards feel free to contact me.
Kind regards,
Holger
SAP Labs Berlin
