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.