Hi, i have strange results (in performace) for this query:

SELECT
PRN00.PRN0CDAG,
PRN10.PRN1PRCL,
PRN20.PRN2PRDT,
PRN30.PRN3ARTC,
ART00.ART0TAB1,
ANA00.ANA0PIVA
FROM
PRN00
INNER JOIN PRN10
ON (PRN00.PRN0CDAG = PRN10.PRN0CDAG
AND PRN00.PRN0ANNF = PRN10.PRN0ANNF
AND PRN00.PRN0RGNR = PRN10.PRN0RGNR)
INNER JOIN PRN20
ON (PRN20.PRN0CDAG = PRN00.PRN0CDAG
AND PRN20.PRN0ANNF = PRN00.PRN0ANNF
AND PRN20.PRN0RGNR = PRN00.PRN0RGNR)
INNER JOIN PRN30
ON (PRN20.PRN2PRDT = PRN30.PRN2PRDT
AND PRN20.PRN0RGNR = PRN30.PRN0RGNR
AND PRN20.PRN0ANNF = PRN30.PRN0ANNF
AND PRN20.PRN0CDAG = PRN30.PRN0CDAG)
INNER JOIN ART00
ON (PRN30.PRN3ARTC = ART00.ART0CODE)
INNER JOIN ANA00
ON (ANA00.ANA0CODE = PRN10.PRN1CLIE AND ANA00.ANA0TREC=1)
remember this --> ^^^^^^^^^^^^^^^^


here is the plan

PLAN JOIN (PRN00 NATURAL,ANA00 INDEX (PK_ANA00),PRN30 INDEX
(PK_PRN30),ART00 INDEX (PK_ART00),PRN20 INDEX (PK_PRN20),PRN10 INDEX
(PK_PRN10))



Execute time: 00:00:26 (tooooooo much).

PRN00: 8 NON indexed reads
PRN10: 3001431 indexed reads
PRN20: 687714 indexed reads
PRN30: 687714 indexed reads
ART00: 687714 indexed reads
ANA00: 51205 indexed reads


NB: as you can only PKs are used.


If I change the sql:

SELECT
PRN00.PRN0CDAG,
PRN10.PRN1PRCL,
PRN20.PRN2PRDT,
PRN30.PRN3ARTC,
ART00.ART0TAB1,
ANA00.ANA0PIVA
FROM
PRN00
INNER JOIN PRN10
ON (PRN00.PRN0CDAG = PRN10.PRN0CDAG
AND PRN00.PRN0ANNF = PRN10.PRN0ANNF
AND PRN00.PRN0RGNR = PRN10.PRN0RGNR)
INNER JOIN PRN20
ON (PRN20.PRN0CDAG = PRN00.PRN0CDAG
AND PRN20.PRN0ANNF = PRN00.PRN0ANNF
AND PRN20.PRN0RGNR = PRN00.PRN0RGNR)
INNER JOIN PRN30
ON (PRN20.PRN2PRDT = PRN30.PRN2PRDT
AND PRN20.PRN0RGNR = PRN30.PRN0RGNR
AND PRN20.PRN0ANNF = PRN30.PRN0ANNF
AND PRN20.PRN0CDAG = PRN30.PRN0CDAG)
INNER JOIN ART00
ON (PRN30.PRN3ARTC = ART00.ART0CODE)
INNER JOIN ANA00
ON (ANA0CODE = PRN10.PRN1CLIE
AND ANA00.ANA0TREC = PRN10.PR10FAKE)

NB: where PRN10.PR10FAKE was create "ad hoc" and PRN10.PR10FAKE = 1
(always)

here is the new plan

PLAN JOIN (PRN00 NATURAL,PRN30 INDEX (PK_PRN30),ART00 INDEX
(PK_ART00),PRN10 INDEX (PK_PRN10),ANA00 INDEX (PK_ANA00),PRN20 INDEX
(PK_PRN20))

Execute time: 00:00:01 :-D

PRN00: 8 NON indexed reads
PRN10: 448 indexed reads
PRN20: 448 indexed reads
PRN30: 91 indexed reads
ART00: 91 indexed reads
ANA00: 448 indexed reads


Why this strange (for me) performace?


PS: I try different solutions (i.e create a view on ANA00 with
ANA00.ANA0TREC=1, ...) but the result does not change.


--
Ciao
L. (Linux User #332241)