Re: Views vs. Direct SQL - Overhead Savings? (Birgitta Hauser)

JDHorn wrote:

While reviewing the sql index advisor, it seems to be
telling me to create a 64KB Binary Radix index over our
customer file (which has only 31,000 records in it)
which currently has a ddl described logical file for it
with exactly the same key (customer#).

times advised-61, est idx creation time 6:59,
most expensive estimate 419, average of query
estimates 108, records in table when advised 31374.

does this seem odd? is it worth it?

it is doing the same thing over some other
(vastly larger) files also.

That the /same/ index is requested is not odd. The /attributes/ of the existing index are not those /preferred/ by the query engine for the fastest access. What is preferred [at least what the engine *thinks* it would prefer] is what is advised.

Probably the CUSTOMER# as key is small [in bytes] so the page size is defaulting to the minimal of 8K. Although the smaller size is typically good for RLA [row level access] and often for journal & cmtctl, that small page size is not conducive for querying due to the small number of rows [key portion] that will fit in that much memory; i.e. eight to one page requests, versus using an SQL INDEX which will by default create with the 64K pagesize.

The INDEX could be created in addition to the existing access path for the cost of one create, its storage, and its maintenance; saving all of the multiple creations [& maintenance while it remains temporary] that currently take place during run-time query activity. Or the INDEX could be created as a replacement for the DDS LF; re-create the DDS LF after the CREATE INDEX, to effect sharing the existing access path [so storage and maintenance is for just the one access path, while keeping separate database logical *FILE objects if desired]. Consider that the replacement method [which will surely be recommended by most w/out regard to any consequences] may have a negative impact on performance of RLA applications and the use of journaling and\or commitment control; i.e. unless sufficient memory is available, the increased page size for the larger memory footprint of the permanent index could be[come] an issue outside of the query activity.

If the original creation(s) of the index or choice not to use that index during runtime is not currently a problem [i.e. the impact to CPU & memory during querying does not affect the users\system], then creating the index could be delayed until resource impacts make the index existence a requirement [assuming positive benefits resulted]. The MTI [materialized temporary index] feature may be reducing the need for creating the INDEX; the query engine may create the index as temporary [and the database maintains], so the extra overhead of the index existence is tempered.

Regards, Chuck