RE: SQL/400 (index building)

[pytel@xxxxxxxxxx]

Do a STRDBG UPDPROD(*YES) before running your query and optimizer will
explain (in a joblog) decisions it made.
Couple of typical things which come to mind:
 - CCSID for access path was different from your job's CCSID
 - you have many indexes and optimizer timed out in interactive job (it
will never time out in a batch run)
There could be other reasons - look at debug messages.

    Alexei Pytel


Joel Fritz <JFritz@sharperimage.com> on 03/08/2000 10:54:20 AM

Please respond to MIDRANGE-L@midrange.com

To:   "'MIDRANGE-L@midrange.com'" <MIDRANGE-L@midrange.com>
cc:
Subject:  RE: SQL/400 (index building)




I certainly don't have deep theoretical knowledge of the query optimizer,
but I have had many experiences where I've built queries in Query 400 where
I've specified ordering for a file that duplicated an existing access path
and the query optimizer chose to build an index.  I've been able to improve
the performance dramatically (time reductions of up to 90%) in these cases
by specifying the logical file in the query.  I know it's not supposed to
work this way, but I'm curious how it might happen.

> -----Original Message-----
> From: pytel@us.ibm.com [mailto:pytel@us.ibm.com]
> Sent: Tuesday, March 07, 2000 2:42 PM
> To: MIDRANGE-L@midrange.com
> Subject: RE: SQL/400
>
>
> Building index from index is not the same as building index from table
> data.
> It normally involves doing preselection on underlying index.
> If optimizer have chosen to build such index, it obviously
> decided that
> this was the fastest way to get a result.
> You cannot "pre-build" such index, so if you think this is a
> concern, try
> to reformulate your query.
>
> PS: as a second thought, you can try to build a view, if you
> can guess what
> kind of preselection was done.
> And then address your query to a view, not to original table.
>
>     Alexei Pytel
>
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to
MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator:
david@midrange.com
+---



+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---