In a cobol program I have an SQL instruction like following:
EXEC SQL DECLARE CURSORE_1 CURSOR FOR
SELECT CAMPO1,
CAMPO2,
CAMPO3,
CAMPO4,
CAMPO5,
CAMPO6,
CAMPO7,
CAMPO8
FROM TABELLA1
WHERE CAMPO3 = :CAMPO3 AND
(CAMPO8 BETWEEN :WS-CAMPO8-FROM AND :WS-CAMPO8-
TO) AND
(CAMPO5 BETWEEN :WS-CAMPO5-FROM AND :WS-CAMPO5-
TO) AND
(CAMPO1 BETWEEN :WS-CAMPO1-FROM AND :WS-CAMPO1-
TO) AND
((CAMPO1 :WS-CAMPO1-FROM) OR
(CAMPO1 = :WS-CAMPO1-FROM AND
CAMPO2 :WS-CAMPO2-FROM) OR
(CAMPO1 = :WS-CAMPO1-FROM AND
CAMPO2 = :WS-CAMPO2-FROM AND
CAMPO8 :CAMPO8XX-FROM) OR
(CAMPO1 = :WS-CAMPO1-FROM AND
CAMPO2 = :WS-CAMPO2-FROM AND
CAMPO8 = :CAMPO8XX-FROM AND
CAMPO4 < :WS-CAMPO4-FROM) OR
(CAMPO1 = :WS-CAMPO1-FROM AND
CAMPO2 = :WS-CAMPO2-FROM AND
CAMPO8 = :CAMPO8XX-FROM AND
CAMPO4 = :WS-CAMPO4-FROM AND
CAMPO5 >= :WS-CAMPO5-FROM))
ORDER BY CAMPO1,
CAMPO2,
CAMPO3,
CAMPO8,
CAMPO4 DESC,
CAMPO5
END-EXEC.
the table contains 16.000.000 records and during the execution of the
program, for this query, optimizer chose to create a temporary index
and indicate in the job log the key field used for building the access
path (I have in QAQQINI MESSAGES_DEBUG = *YES).
The field used for the key are the same ones of the ORDER BY and I
already have an index like this.
I don't know why the optimizer does not use the existing index (in
some conditions the query durations exceeds 5 minutes)
Can someone help me ?
Thanks