We have a problem in choosing the Access path like if we execute from spufi, it takes one Access path that is actually good one which takes few seconds to retrieve the datas , but if we execute from Application program it takes some other Access path and it's relatively very small.
This was happened after REORG and also the version change..
the below is the SQL Statements,
EXEC SQL DECLARE A CURSOR FOR
FROM C#FORM_TABLE_09 A
, C#HISTORY_07 B
, C#ACTIONS_07 C
, C#FORM_TYPES_07 D
, C#FORM_STATS_02 F
WHERE D.FORM_TYPE_NAME BETWEEN T1
AND D.APPLICATION_ID BETWEEN :LOW_APPL
AND F.APPLICATION_ID = D.APPLICATION_ID
AND F.FORM_TYPE = D.FORM_TYPE
AND F.INACTIVE_CTR = 0
AND F.NEXT_NODE = :NODE#
AND A.FORM_TYPE = F.FORM_TYPE
AND A.COMPANY_CODE = F.COMPANY_CODE
AND A.FORM_NUMBER = F.FORM_NUMBER
AND A.APPLICATION_ID = F.APPLICATION_ID
AND B.FORM_TYPE = A.FORM_TYPE
AND B.COMPANY_CODE = A.COMPANY_CODE
AND B.FORM_NUMBER = A.FORM_NUMBER
AND B.SUBFORM_NUMBER = F.SUBFORM_NUMBER
AND B.APPLICATION_ID = A.APPLICATION_ID
AND B.SEQUENCE_NUMBER = F.STATUS_SEQ_NR
AND C.FORM_TYPE = B.FORM_TYPE
AND C.ACTION_NUMBER = B.ACTION_NUMBER
AND C.APPLICATION_ID = B.APPLICATION_ID
FOR FETCH ONLY;
Could you please check the above query which table should Access first whether it's bigger ones or smaller ones?
Please give me your valuable suggestions in this regard.