SQL optimization magic

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

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