Re: SQL optimization magic

Well, there is no magic.
I believe it was documented since times immemorial, that optimizer uses
indexes (access paths) to find statistical information about value
distribution in the file. And I remember there was a recommendation (maybe
in a redbook or perhaps SQL class) to build unused indexes over large files
to help optimizer to make better choice for frequent queries.
In V5R2, there is a new statistical manager, which can collect statistics
without indexes:

http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/rzajq/rzajqmststatsmanager.htm



    Alexei Pytel
always speaking for myself




                      Vern Hamberg
                      <vhamberg@centerfieldtech        To:       
midrange-l@midrange.com
                      nology.com>                      cc:
                      Sent by:                         Subject:  SQL 
optimization magic
                      midrange-l-admin@midrange
                      .com


                      11/15/2002 03:05 PM
                      Please respond to
                      midrange-l





Hi all

Joe Pluta, I think, was asking about the black box nature of SQL. I agree.
And here's an interesting one - maybe even a good tip:

The tip is, have a recommended index around, even if it is not used by the
optimizer.

I have an SQL SELECT that is the basis of a view. It is a join of 3 files.
The file I'm discussing has about 600,000 rows, of which c.320,000 are
returned. So a table scan is really the best access method, due to the
large number of rows returned.

When the index (built on the join field between two of the tables) does not
exist, the optimizer builds a temporary index. This takes almost a minute
to do. Time estimate is 58 seconds.

When the index DOES exist, the optimizer does NOT build the temporary
index, it uses the table scan. Estimated time, 2 seconds.

The optimizer needs information upon which to make decisions. I think that,
if it sees a join, it'll think that an index will help that, having no
other information. So it builds the index and probably uses it, whether
better or not.

If the index exists, there is information that can help decide the better
access method.

Long-running queries are always helped less by the optimization stage,
unless the optimizer chooses the less efficient method.

With index but not used, 17 seconds to return 50 records.
Without index, temp index built, 61 seconds to return 50 records.

Eh? Whadya think?

Vern


_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.