SELECT Problem

[Dirk Vleugels]

Hello,

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.

Any hints, workarounds?

Regards,
Dirk

-- 
Dirk Vleugels                           E-Mail: dvl@stripped
2scale GmbH                             Phone:  [phone]
Barcelonaweg 14                         Fax:    [phone]
44269 Dortmund, Germany                 Mobile: [phone]