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
+---