Re: SQL sort

On Thu, Sep 3, 2015 at 4:14 PM, Booth Martin <booth@xxxxxxxxxxxx> wrote:

On large files with data that will be used in various applications and in
various sort-by schemes, are logical files a help?
If so, does one reference the physical file and then SQL finds the best
logical, or does it help to specify a specific logical file?

​Maybe​ :)

Unlike RLA, you don't need the exact access path for everything you want to
do. Lets say you have WHERE MYFLD1 = 1 AND MYFLD2 = 2. With RLA, you
might need an LF with both fields. With SQL, if you have an index over
MYFLD1 and one over MYFLD2, the SQE can put them together to figure out
which records you need. May not be quite as fast as having the "perfect"
index. But the advantage is you need a lot less indexes in total.

Take a look here
https://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/rzajq/rzajqprint.htm

Paying particular attention to the indexing strategies section.

But don't kill yourself trying to setup indexes/LF you think you need.
Build the queries you need, run them. If they are to slow, run them
through Visual Explain and see what's going on and what indexes it thinks
it needs. Note that SQL performance is highly dependent on environment.
You can't really tune a production SQL query on a development box. You
have to use the tools on the production box.

What is the right way to collect a set of records equal to SFLPAGE?
​​

​That depends...
FETCH FIRST XXX ROWS ONLY, will limits the rows in the ODP to just the
first XXX rows.

But you'd probably want to do a block (aka multiple-row) fetch from a
cursor:

​fetch from mycursor
for XXX rows into :myarray;

​
​Charles​


​