SQL Optimizer

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

frenk_mo@hotmail.com - 26 Feb 2007 17:14 GMT

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

Reply to this Message

Tonkuma - 27 Feb 2007 05:07 GMT

Did you specify DESC for CAMPO4 in your index?

Reply to this Message

frenk_mo@hotmail.com - 27 Feb 2007 08:45 GMT

> Did you specify DESC for CAMPO4 in your index?

the index created is:
CREATE UNIQUE INDEX TABELLAI1 ON TABELLA1
(CAMPO1, CAMPO2, CAMPO3, CAMPO8, CAMPO4 DESC, CAMPO5 );

like indicated in joblog...

Reply to this Message

Tonkuma - 27 Feb 2007 10:51 GMT

How about this index? (Expecting more matching with WHERE clause)

CREATE UNIQUE INDEX TABELLAI2 ON TABELLA1
(CAMPO3, CAMPO1, CAMPO8, CAMPO5, CAMPO2, CAMPO4);

Reply to this Message

frenk_mo@hotmail.com - 27 Feb 2007 14:29 GMT

> How about this index? (Expecting more matching with WHERE clause)
>
> CREATE UNIQUE INDEX TABELLAI2 ON TABELLA1
> (CAMPO3, CAMPO1, CAMPO8, CAMPO5, CAMPO2, CAMPO4);

thanks for the suggestion, unfortunately this index has no effects;

the message in joblog are:

The OS/400® Query optimizer considered all access paths built over
member TABELLA1 of file TABELLA1 ...
...
Following each access path name in the list is a reason code which
explains why the access path was not used. A reason code of 0
indicates that the access path was used to implement the query.
LIBRARY/TABELLAI2 5, LIBRARY/TABELLAI1 4
...
4 - The cost to use this access path, as determined by the optimizer,
was higher than the cost associated with the chosen access method.
5 - The keys of the access path did not match the fields specified for
the ordering/grouping criteria. For distributed file queries, the
access path keys must exactly match the ordering fields if the access
path is to be used when ALWCPYDTA(*YES or *NO) is specified.
...
...

A temporary access path was built to access records from member
TABELLA1 of file TABELLA1 in library LIBRARY for reason code 1
( Perform specified ordering/grouping criteria)
...
The access path was built using the following key fields. The key
fields and their corresponding sequence (ASCEND or DESCEND) will be
shown:
(CAMPO1 ASCEND, CAMPO2 ASCEND, CAMPO3 ASCEND, CAMPO8
ASCEND, CAMPO4 DESCEND, CAMPO5 ASCEND )

And this is the index TABELLAI1 already in the system...

Reply to this Message