Re: Permanent Access Paths and SQL

Jonathan Mason wrote:

We have an SQL statement that occasionally runs really slowly (up to 120
seconds). When I run the statement interactively in debug mode, the
joblog shows a recommendation that a permanent access path is built as
that may help the query run faster.

The SQL runs over a logical file that is keyed on account number plus
another five sub-keys and only contains records with the open amount
field set to zero. The logical file is joined to the physical file
using a left outer join and relative record numbers (amongst other
criteria) and the resulting data is sequenced to match the sequence from
the logical file.

The suggestion though, says to create a permanent access path for the
logical file and then says to base it over the physical. The question I
have though is how do you create a permanent access path?

From reading various references, I got the impression that this meant

use the CREATE INDEX statement, but that just creates a logical file and
as the logical file I want already exists it doesn't seem to make much
sense to create another one.

Thanks

Jonathan



_______________________________________________________
This message was sent using NOCC v1.14 webmail software
_______________________________________________________

I recommend NOT selecting from the logical file. Select from the table
(physical file) and let the query optimizer decide which access paths to
use. Either use visual explain or run the query in debug to produce the
"suggestion" messages, and see if there is a difference. The debug
information may give you information. There is a reason code that
describes why a specific choice of access path was made, or why one was
created (usually because it's less work.)